Excel Scenario Manager: How It Works and When to Use It

Excel Scenario Manager saves named sets of input values and lets you switch between them to see how a formula-driven model changes. In Microsoft 365 for Windows, open Data > What-If Analysis > Scenario Manager, add scenarios by selecting their changing cells, and choose a result cell when you create a summary report.

What Scenario Manager does

Scenario Manager is Excel’s built-in tool for comparing a small number of distinct “what if?” cases in one workbook. A scenario is a saved collection of values for selected worksheet cells. When you display a saved scenario, Excel puts those values into the changing cells; formulas that depend on them recalculate. You can then inspect the result on the worksheet or create a report that places scenarios side by side.

The feature does not make assumptions for you, forecast an outcome, or decide which case is most likely. You supply the assumptions and build the formulas that calculate the outcomes. For example, a sales plan might have separate assumptions for price, unit cost, fixed expenses, and units sold. Scenario Manager can store a conservative, expected, and stronger-sales case for those inputs, then show the calculated profit for each.

That distinction matters: the scenarios are collections of inputs, while formulas and reports provide the outputs. If the result formula is missing or does not refer to the changing cells, switching scenarios will not produce a meaningful comparison.

A simple example of scenarios

Suppose a worksheet has labels in A2:A6 and these values in B2:B6: selling price in B2, unit cost in B3, fixed costs in B4, units sold in B5, and profit in B6. Set B2 to 50, B3 to 30, B4 to 2000, and B5 to 100. With that layout, enter this formula in B6:

=(B2-B3)*B5-B4

The result is 0: a 20 contribution per unit multiplied by 100 units gives 2,000, which exactly covers the fixed costs. Now a conservative scenario could use a price of 47, unit cost of 31, fixed costs of 2,200, and 90 units sold. The calculation is 16 × 90 − 2,200, or a loss of 760. An upside scenario could use 52, 29, 1,800, and 120; the calculation is 23 × 120 − 1,800, or profit of 960.

Those three cases illustrate what the tool stores: four values for each named case. The formula in B6 stays in place and recalculates as the inputs change. These figures are illustrative, not a forecast; a useful model should make its assumptions visible and use values appropriate to the situation being analyzed.

How to create and show a scenario

Before opening the tool, identify the input cells that should vary and check that your output formulas refer to them. For the example, the changing cells are B2:B5; B6 is a result cell and should not be included among the inputs. In Excel for Microsoft 365 on Windows, follow these steps:

Choose Data > What-If Analysis > Scenario Manager.
Select Add. Enter a clear scenario name, such as Conservative, and specify the changing cells, for example B2:B5.
Select OK. In the Scenario Values dialog, enter the values for that case in the same order as the changing cells, then select OK.
Repeat Add for each additional case, entering a distinct name and its input values.
To apply one case to the worksheet, select its name in Scenario Manager and choose Show. The changing cells take that scenario’s values and dependent formulas recalculate.

Use Edit to change a saved scenario’s name or values, and Delete to remove one. Scenario Manager supports up to 32 changing cells in a scenario, so it is designed for compact models rather than a long list of every cell in a large workbook. Use inputs that represent independent assumptions; avoid selecting a result formula as a changing cell, because displaying a scenario would replace that cell’s existing contents.

Showing a scenario changes the worksheet’s current input values. It does not permanently lock the model to that case: choose another saved scenario to display its values. Still, keep a clearly labeled base case or a copy of the original inputs so it is easy to return to the starting assumptions. Save the workbook to retain its scenarios.

Creating a scenario summary report

A summary report makes comparison easier when you do not want to click Show repeatedly and write down each result. In Scenario Manager, select Summary, choose Scenario summary, and specify the result cell or cells—for example, B6 for profit. Excel creates a separate worksheet showing the changing cells and selected results for the saved scenarios. Give result cells understandable labels, because a report is more useful when readers can tell what each number represents.

Some desktop versions also offer a Scenario PivotTable report. It presents scenario information in a PivotTable-style layout that can be rearranged for analysis. Choose the report type available in your Excel version; for a straightforward comparison, the ordinary Scenario summary is often sufficient. A report is a generated view, not a live dashboard that automatically follows every later model edit. If you change the model or scenarios and need an up-to-date comparison, create a fresh report and check that it reflects the assumptions you intend to compare.

For a readable report, keep the number of output cells focused. Useful outputs might include profit, cash needed, or capacity used, provided those measures are calculated in the workbook. Too many outputs or poorly labeled input cells make the report harder to interpret. A short note in the worksheet can explain what distinguishes each scenario and why its assumptions were chosen.

Build a model that is easy to compare

Scenario Manager works best when the worksheet separates assumptions from calculations. Put inputs in a clearly marked area, formulas in a separate results area, and use labels that identify units and time periods. If B2 is a price per unit and B5 is annual units sold, label them that way rather than using vague names such as “Value 1.” Consistent units help prevent comparisons that look precise but mix monthly and annual figures.

Before saving scenarios, check the model with a hand calculation or a small test case. Confirm that changing each input has the expected effect on the outputs, and make sure formulas do not accidentally point to a different worksheet or an old assumption. It can also help to keep a baseline scenario representing the current plan. A baseline lets readers compare alternatives against a reference instead of treating every case as an isolated number.

Names should describe the assumptions, not imply certainty. “Lower demand” or “Higher materials cost” is more informative than “Scenario 2.” If several assumptions change at once, record the reason in a nearby note or the scenario’s comment field, where available. A scenario is only as clear as the explanation of how its values differ.

Scenario Manager compared with other what-if tools

Excel offers several ways to explore assumptions, and the right choice depends on the question:

Scenario Manager: Stores named sets of values for multiple inputs and lets you compare the outcomes of discrete cases.
Goal Seek: Works backward from a target result by changing one input cell. For example, it can find the sales volume needed to reach a specified profit.
Data Tables: Show how one or two input variables affect a formula across a range of values. They are better suited to a grid of sensitivity results than to a few named bundles of assumptions.
Solver: Can search for a value that maximizes or minimizes an objective while observing constraints. It is a different kind of optimization tool, not simply a scenario list.

For instance, if you want to compare three coherent plans in which price, costs, and demand all change together, Scenario Manager is a natural fit. If you want to see profit at every price from 40 to 60 while holding other inputs steady, a Data Table may communicate the range more effectively. These tools answer related but different questions, and they can be used in the same workbook.

Limits and common problems

The 32-changing-cell limit is the most important structural constraint. If a model requires dozens or hundreds of assumptions to vary together, consider simplifying the model, using a separate table of assumptions, or using formulas and other analysis tools to organize the cases. Scenario Manager also stores specific sets rather than automatically sampling a range of possible values. It does not assign probabilities, calculate risk, or identify the best case.

A frequent source of confusion is choosing the wrong cells. If an output does not change when you display another scenario, check that the changing cells include the relevant inputs and that the output formula actually refers to them. If a formula disappears after switching, a formula cell was probably included as a changing cell. Edit the scenario and select input cells instead. Also check that the values were entered in the same order as the changing-cell references.

Another issue is mistaking the report for an independent calculation. The report presents scenario values and selected results from the workbook; it cannot correct a flawed formula or an unrealistic assumption. Verify the model and inspect the inputs before relying on a comparison. For a workbook shared with other people, explain which scenario is currently displayed on the main sheet, since the visible input values may not be the baseline.

Windows, Mac, and Excel for the web

These steps are for Excel for Microsoft 365 on Windows. In Excel for Mac, Scenario Manager is also available in desktop Excel; look on the Data tab under What-If Analysis and use the Scenario Manager command. Ribbon placement can vary with the window size or customized ribbon, but the feature’s basic purpose is the same. Keyboard shortcuts are not needed for the steps above.

Excel for the web does not provide the full desktop Scenario Manager workflow for creating and managing scenarios. If you need to add, edit, show, or generate reports for scenarios, open the workbook in desktop Excel. When a workbook relies on desktop-only features, check the file in the desktop app before sharing or making decisions based on its results.

Frequently Asked Questions

What is Scenario Manager used for in Excel?
It saves named groups of input values so you can apply different assumptions to a model and compare the resulting calculations. It is useful when each case changes several inputs together.

How many changing cells can a scenario have?
A scenario can use up to 32 changing cells. If your comparison depends on more inputs, reduce the number of variables where practical or consider a different model design.

Does Scenario Manager calculate the result by itself?
No. It changes the selected input cells, and Excel recalculates formulas that depend on them. You must create and verify the formulas that calculate the outcome.

Can I compare scenarios side by side?
Yes. Select Summary in Scenario Manager and create a Scenario summary report, then choose the result cells to include. The report displays saved cases together; recreate it if you need a report reflecting later changes.

Is Scenario Manager available in Excel for the web?
The full tool for creating and managing scenarios is a desktop Excel feature. Use Excel for Microsoft 365 on Windows or desktop Excel for Mac to work with scenarios, then verify the workbook there.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.