If you are tired of overwriting a forecast every time you test a new assumption, Excel’s What-If Analysis tools let you experiment without changing your source values. Scenario Manager, Goal Seek, and Data Tables are all under Data > What-If Analysis.
What Is What-If Analysis in Excel?
What-If Analysis is a group of three built-in Excel tools that let you swap input values and instantly see how your formulas respond, without manually editing cells. Each tool solves a slightly different problem:
- Scenario Manager: Compare named sets of inputs side by side (e.g., low sales vs. high sales)
– Goal Seek: Work backwards. Tell Excel the result you want, and it finds the input that gets you there
– Data Tables: Run sensitivity analysis across a range of values automatically
The widget profit example below is deliberately simple so the mechanics are obvious. Once you see how it works, you can apply the same logic to any formula-driven spreadsheet.
How to Use Scenario Manager
Scenario Manager is the most visual of the three tools. You define named scenarios, each with its own set of input values, and switch between them with one click, or dump them all into a summary table.
For this example, assume you sell widgets at a $10 profit each. Your spreadsheet has:
- A3: Number of widgets sold
– B3: Profit per widget ($10)
– C3: Total profit, calculated with =A3*B3
You want to see your profit at 100, 200, and 300 widgets sold, without editing A3 three separate times.
Step 1: Open Scenario Manager
- Click the Data tab on the Ribbon.
2. In the Forecast group, click What-If Analysis.
3. Select Scenario Manager from the dropdown.
Step 2: Add your first scenario
- In the Scenario Manager window, click Add.
2. In the Scenario name field, type 100 Widgets.
3. In the Changing cells field, type A3.
4. Click OK.
- In the Scenario Values window, type 100 and click OK.
Step 3: Add the remaining scenarios
- Click Add again in the Scenario Manager window.
2. Name the scenario 200 Widgets, set changing cell to A3, click OK, and enter 200 as the value.
3. Repeat for a third scenario named 300 Widgets with a value of 300.
You should now see all three scenarios listed in the Scenario Manager window.
Step 4: Switch between scenarios
- Click 100 Widgets in the list, then click Show. Cell A3 updates to 100 and C3 shows $1,000.
2. Click 200 Widgets and click Show. C3 updates to $2,000.
3. Click 300 Widgets and click Show. C3 updates to $3,000.
Step 5: Generate a scenario summary
- Click the Summary button in the Scenario Manager window.
2. In the Result cells field, type C3.
3. Click OK.
Excel creates a new worksheet called Scenario Summary that shows all three scenarios and their resulting profit values side by side.
How to Use Goal Seek
Goal Seek works in reverse: you tell Excel the result you want in a formula cell, and it figures out what input value gets you there. It’s perfect for questions like “How many widgets do I need to sell to hit $5,000 profit?”
- Click the Data tab, then What-If Analysis > Goal Seek.
2. In the Set cell field, enter C3 (your profit formula cell).
3. In the To value field, type 5000.
4. In the By changing cell field, enter A3 (widgets sold).
5. Click OK.
Excel iterates through values and lands on 500, meaning you need to sell 500 widgets to hit $5,000 profit. If Goal Seek returns an error, check that C3 contains a formula that actually references A3. A static value in C3 gives Goal Seek nothing to work with.
How to Use Data Tables
Data Tables run the same formula across an entire range of input values automatically, with no manual swapping required. They’re the fastest way to build a sensitivity table.
In this example, you vary the profit per widget in B3 while keeping the quantity in A3 fixed.
One-variable Data Table (e.g., profit at prices $10–$15)
- In column A, list your input values vertically (e.g., A5: 10, A6: 11, A7: 12, A8: 13, A9: 14, A10: 15).
2. In the cell one column to the right and one row above your first input value (B4), enter a reference to your profit formula: =C3.
3. Select the range A4:B10 (the formula cell, all input values, and the blank result column).
4. Click Data > What-If Analysis > Data Table.
5. Leave Row input cell blank. In Column input cell, enter B3 (profit per widget).
6. Click OK.
Excel fills in the profit for each price in column B. If the result column stays blank, double-check that your Column input cell points to the cell your formula actually depends on.
Excel also supports two-variable Data Tables when you need to vary two inputs, such as price and quantity. Those tables use both a row input cell and a column input cell.
Using Copilot and Python for Larger Analyses
If you’re on Microsoft 365, Copilot can help analyze a selected data range and suggest ways to summarize the results. However, do not assume it will configure Scenario Manager or run Python automatically; those workflows are not guaranteed by Microsoft’s public documentation.
For a larger analysis, you can use Copilot to help interpret your data, then build the calculation with Excel’s standard tools:
- Select the data you want to analyze.
2. Ask Copilot to identify trends or suggest a useful summary.
3. Verify its suggestions against your formulas and source data.
For advanced sensitivity analysis across hundreds of combinations, Python in Excel is another option. You write the Python calculation in a cell yourself, then use tools such as a pandas DataFrame to organize the results. This is more work than a built-in Data Table, but it gives you more control over a large model.
Common What-If Analysis Errors (and Quick Fixes)
| Error | Cause | Fix |
|---|---|---|
| “Cell must contain a value” | Goal Seek’s Set cell contains a static number, not a formula | Replace the value with a formula referencing the changing cell |
| Goal Seek can’t find a solution | Sheet is protected or calculation is set to manual | Review > Unprotect Sheet; Formulas > Calculation Options > Automatic |
| Data Table results are all blank | Wrong input cell selected | For a column of inputs, use Column input cell, not Row input cell |
| Scenario Manager won’t accept changing cells | More than 32 changing cells selected | Limit to 32 cells; use Copilot or a Data Table for larger ranges |
Wrapping Up
Scenario Manager is the right starting point for most people. It’s visual, it’s reversible, and the summary report is a nicely done one-click deliverable you can drop straight into a meeting. Goal Seek earns its keep the moment you need to back-solve a target number, and Data Tables shine for sensitivity analysis across a continuous range. If you’re on Microsoft 365 and haven’t tried the Copilot prompt approach yet, it’s worth a look. It handles the setup work smoothly and gets you to results faster than clicking through the dialogs manually.
