Excel Scenario Manager Tutorial: Compare What-If Plans

To use Excel Scenario Manager, build a worksheet model, then select Data > What-If Analysis > Scenario Manager > Add to save different sets of input values. You can switch among those scenarios to see how the model’s results change, or create a Scenario Summary report to compare them side by side.

What Scenario Manager is for

Scenario Manager is Excel’s built-in tool for what-if analysis. It saves named groups of values for selected input cells, then substitutes a group into your worksheet when you show that scenario. Formulas that depend on those cells recalculate, so you can compare the consequences of different assumptions without repeatedly typing values into the model.

For example, a small business might compare a baseline, stronger sales, and weaker sales plan. Each plan could include a different selling price, unit volume, variable cost, and fixed cost. Scenario Manager keeps those assumptions together under recognizable names. It does not predict which plan will happen, decide which one is best, or explain why an outcome occurs; the worksheet formulas and the assumptions you provide determine the results.

This feature is useful when you have a handful of meaningful cases, such as best-case, expected-case, and cautious-case estimates. It is less suitable for exploring every possible value in a large range. The distinction matters: scenarios are named bundles of inputs, not a probability forecast or an automatic sensitivity analysis.

Prepare a worksheet model

Before opening Scenario Manager, separate assumptions from calculations and label the cells clearly. The example below uses one worksheet, with labels in column A and values or formulas in column B:

B2: selling price per unit, initially 50.
B3: units sold, initially 1,000.
B4: variable cost per unit, initially 30.
B5: fixed costs, initially 12,000.
B6: revenue, calculated from price and units.
B7: total cost, calculated from variable and fixed costs.
B8: profit, calculated as revenue minus total cost.

With selling price in B2 and units sold in B3, enter this formula in B6:

=B2*B3

With units sold in B3, variable cost per unit in B4, and fixed costs in B5, enter this formula in B7:

=B4*B3+B5

With revenue in B6 and total cost in B7, enter this formula in B8:

=B6-B7

At the starting assumptions, revenue is 50,000, total cost is 42,000, and profit is 8,000. These figures are illustrative, not a forecast. Use cell references in the calculation formulas rather than typing assumptions directly into them; otherwise, changing a scenario input may not affect the result as intended.

Create and compare scenarios step by step

In Excel for Microsoft 365 on Windows, follow these steps. The worksheet should contain the model and the input cells you plan to vary.

Open the Data tab, select What-If Analysis, and choose Scenario Manager.
In the Scenario Manager dialog, select Add. Enter a descriptive name such as Baseline.
In Changing cells, select the assumption cells B2:B5. These are the cells whose values the scenario will store. Avoid including formula cells B6:B8.
Select OK. In the Scenario Values dialog, enter the baseline values: 50, 1000, 30, and 12000, in the order of the selected cells. Select Add to save this scenario and begin another, or select OK to finish.
Add a Strong sales scenario using values 55, 1200, 31, and 13000. Add a Cautious scenario using 48, 850, 32, and 12500. Keep the changing-cell selection consistent so the cases are easy to compare.
Back in Scenario Manager, select a scenario name and choose Show to place its values into the worksheet. Check the recalculated results in B6:B8.

For the Strong sales case, revenue is 55 × 1,200, or 66,000. Total cost is 31 × 1,200 + 13,000, or 50,200, so profit is 15,800. For Cautious, revenue is 48 × 850, or 40,800; total cost is 32 × 850 + 12,500, or 39,700; profit is 1,100. These arithmetic checks help confirm that the scenario assumptions were entered in the intended order.

Generate a Scenario Summary report

A summary report is often easier to interpret than switching between scenarios and noting results manually. In Scenario Manager, select Summary. Choose Scenario summary, then specify the result cells you want to compare, such as B6:B8, and confirm. Excel creates a new worksheet containing the scenarios, their changing values, and the selected results.

Choose result cells that answer the decision you are examining. In the example, revenue, total cost, and profit give more context than profit alone: two plans can produce similar profit through very different sales and cost assumptions. Give the inputs and results clear worksheet labels so the report is understandable when viewed separately from the model.

The report is a generated worksheet, not a live dashboard that automatically refreshes whenever you edit the model or its scenarios. If you change assumptions or formulas and need an up-to-date comparison, create a new summary report. Excel may leave earlier report sheets in the workbook, so rename or remove outdated ones to avoid confusing readers.

Edit, show, and remove saved scenarios

To revisit a saved case, open Data > What-If Analysis > Scenario Manager, select its name, and choose Show. The displayed scenario values are applied to the changing cells in the worksheet. Showing a scenario changes the worksheet’s current inputs; it does not create a separate copy of the model for each case. If you want to preserve the original values, note them or save a copy of the workbook before experimenting.

To revise a scenario, select it in Scenario Manager and choose Edit. You can change its name, its changing-cell references, or the saved values. If you change which cells are used, check the values carefully because their order must correspond to the changing-cell list. To remove a scenario, select it and choose Delete. Deleting a saved scenario is different from deleting a worksheet or removing a formula.

Scenarios are saved with the workbook, so a colleague who opens the file in a compatible desktop version can use them. Explain the meaning and units of your inputs in the sheet: a value of 30 might mean 30 dollars per unit, 30 percent, or something else. Scenario names such as Low volume or Higher material cost are more informative than Scenario 1 and Scenario 2.

Limits and common mistakes

Scenario Manager allows up to 32 changing cells in a single scenario. That limit encourages focused models but can be restrictive when a plan depends on many independent assumptions. It is usually better to group related assumptions in a compact input section and vary only cells that genuinely distinguish the cases.

Including outputs as changing cells: Select assumptions, not calculated result cells. Otherwise, a scenario may overwrite a formula with a stored value.
Entering values in the wrong order: Match each Scenario Values entry to the order shown for the changing cells. Use labels beside the inputs and review the displayed results.
Expecting automatic updates: A summary worksheet is a report generated at a point in time. Recreate it after relevant edits.
Confusing a scenario with a saved workbook copy: Scenarios share one worksheet model. Showing one changes the active input cells; it does not isolate every case in its own worksheet.
Using unclear or inconsistent units: Keep assumptions comparable. If one case uses monthly costs and another uses annual costs, the resulting comparison is misleading.

Scenario Manager stores values, not a complete explanation of the assumptions behind them. Keep notes about sources, dates, and reasoning in the workbook when those details matter. This is especially helpful when assumptions are revised over time or reviewed by someone who did not build the model.

Scenario Manager versus other what-if tools

Choose the tool based on the question. Scenario Manager is suited to comparing several named combinations of inputs. Goal Seek is more direct when you know the result you want and need Excel to find the value for one input cell. For instance, it can find the unit sales required to reach a specified profit, provided the model’s formulas connect that input to the result.

Data Tables are useful for seeing how one or two input variables affect a formula across a range of values. They are a better fit for a grid of price and volume combinations than creating dozens of individually named scenarios. Solver is designed for optimization problems with constraints, such as maximizing an objective while respecting limits; it is a different tool and may require enabling the Solver add-in.

These tools answer different questions, so one is not universally better. A practical workflow might use Scenario Manager to compare a few management plans, then a Data Table to examine a range of prices, or Goal Seek to find the sales level needed for a target. Scenario Manager does not calculate likelihoods or assign probabilities to its cases.

Excel for Mac and Excel for the web

The main instructions above are for Excel for Microsoft 365 on Windows. Excel for Mac also has Scenario Manager in the desktop application, although ribbon placement and dialog appearance can vary by release. Look on the Data tab for What-If Analysis and Scenario Manager. If the exact control is not visible, update Excel or use the application’s command search rather than assuming the feature is absent.

Excel for the web does not provide the full desktop Scenario Manager authoring workflow. Open the workbook in the desktop Excel application to create, edit, or manage scenarios and generate the desktop summary report. A workbook may still contain sheets or results created on desktop, but do not assume that the browser version can manage the saved scenarios in the same way.

Frequently Asked Questions

How many changing cells can Scenario Manager use?
Each scenario can use up to 32 changing cells. If your case requires more inputs, consider simplifying the model or using another analysis approach, such as a data table or separate documented assumptions.

Can Scenario Manager change formulas?
It can apply saved values to the cells designated as changing cells, so selecting formula cells can replace their formulas with values. Select input cells instead, and keep calculated outputs outside the changing-cell range.

Why does my Scenario Summary not update?
The summary is a generated report worksheet rather than a live-linked comparison. Create a fresh summary after changing scenario values, formulas, or other model details, and distinguish the new report from older copies.

Can I compare scenarios without changing my original inputs?
Showing a scenario applies its values to the model’s changing cells. Save or duplicate the workbook first if you need to preserve the current state, or record the original assumptions so you can restore them afterward.

Is Scenario Manager available in Excel for the web?
The full Scenario Manager tools are available in desktop Excel, not as a complete authoring and management workflow in Excel for the web. Use Open in Desktop App to work with scenarios when the browser version does not expose the required commands.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.