Usually after you can create a formula, you can type in new data to see how the formula calculates a new result. However, Excel also offers a feature known as Goal Seeking. With Goal Seeking, you specify the value you want a formula to calculate, and then Excel changes the data in the formula’s cell references to tell you what values you need to achieve that goal. For example, suppose you have a formula that calculates how much money you make every month by selling a product such as cars. Change the number of cars you sell, and Excel calculates your monthly commission. But if you use Goal Seeking, you can specify you want to earn $5,000 for your monthly commission, and Excel will work backward to tell you how many cars you need to sell. As its name implies, Goal Seeking lets you specify a goal and see what number, in a specific cell, needs to change to help you reach your goal. To use Goal Seeking, follow these steps:

1. Click in the cell that contains a formula.

2. Click the Data tab.

3. Click the What-If Analysis icon in the Data Tools group.

4. Click Goal Seek.

5. Click in the To Value text box and type a number that you want to appear in the formula stored in the cell that you clicked in Step 1.

6. Click in the By Changing Cell text box and click one cell that contains data used by the formula you chose in Step 1.

7. Click OK.

8. Click OK (to keep the changes) or click Cancel (to display the original values your spreadsheet had before you chose the Goal Seek command).

Creating Multiple Scenarios

Spreadsheets show you what happened in the past. However, you can also use a spreadsheet to help predict the future by typing in data that represents your best guess of what might happen. When you use a spreadsheet as a prediction tool, you may create a best-case scenario (where customers flood you with orders) and a worst-case scenario (where hardly anybody buys anything). You could type in different data to represent multiple possibilities, but then you’d wipe out your old data. For a quick way to plug different data in the same spreadsheet, Excel offers scenarios. A scenario lets you define different data for multiple cells. That way, you can choose a scenario to plug in one set of data, and then switch back to your original data without retyping everything.

Creating a scenario

Before you can create a scenario, you must first create a spreadsheet with data and formulas. Then you can create a scenario to define the data to plug into one or more cells. To create a scenario, follow these steps:

1. Click the Data tab.

2. Click the What-If Analysis icon in the Data Tools group. A pull-down menu appears.

3. Click Scenario Manager. The Scenario Manager dialog box appears.

4. Click Add.

5. Click in the Scenario Name text box and type a descriptive name for your scenario, such as Worst-case or Best-case.

6. Click in the Changing Cells text box.

7. Click a cell in your spreadsheet that you want to display different data. If you want to choose multiple cells, hold down the Ctrl key and click multiple cells.

8. Click in the Comment text box and type any additional comments you want to add to your scenario, such as any assumptions your scenario made.

9. Click OK

10. Type a new value for each cell.

11. Click OK.

12. Click Show. Excel replaces any existing data with the data you typed in Step 10.

13. Click Close. The data from your scenario remains in the spreadsheet.

Viewing a scenario

After you create one or more scenarios, you can view them and see how they affect your data. To view a scenario, follow these steps:

1. Click the Data tab.

2. Click the What-If Analysis icon in the Data Tools group. A pull-down menu appears.

3. Choose Scenario Manager. The Scenario Manager dialog box appears.

4. Click the name of the scenario you want to view.

5. Click Show. Excel shows the values in the cells defined by your chosen scenario.

6. Click Close.

Editing a scenario

After you create a scenario, you can always change it later by defining new data. To edit a scenario, follow these steps:

1. Click the Data tab.

2. Click the What-If Analysis icon in the Data Tools group. A pull-down menu appears.

3. Choose Scenario Manager. The Scenario Manager dialog box appears.

4. Click the name of the scenario you want to edit and click Edit.

5. (Optional) Edit the name of the scenario.

6. Click in the Changing Cells text box. Excel displays dotted lines around all the cells that the scenario will change.

7. Press Backspace to delete cells, or hold down the Ctrl key and click additional cells to include in your scenario.

8. Click OK. The Scenario Values dialog box appears.

9. Type new values for your cells and click OK when you’re done. The Scenario Manager dialog box appears again.

10. Click Show to view your scenario, or click Close to make the Scenario Manager dialog box disappear.

Viewing a scenario summary

If you have multiple scenarios, it can be hard to switch back and forth between different scenarios and still understand which numbers are changing. To help you view the numbers that change in all your scenarios, you can create a scenario summary. A scenario summary displays your original data along with the data stored in each scenario in a table. By viewing a scenario summary, you can see how the values of your spreadsheet can change depending on the scenario. To create a scenario summary on a separate sheet in your workbook, follow these steps:

1. Click the Data tab.

2. Click the What-If Analysis icon in the Data Tools group. A pull-down menu appears.

3. Choose Scenario Manager. The Scenario Manager dialog box appears.

4. Click Summary. The Scenario Summary dialog box appears.

5. Select the Scenario Summary radio button.

6. Click in the Result Cells text box and then click in a cell that contains a formula that your scenario affects.

7. Click OK.