Scenario Management in Excel: Compare What-If Cases

Formulas & functions · Updated November 21, 2026

In Excel for Microsoft 365 on Windows, use Data > What-If Analysis > Scenario Manager to save different sets of input values and compare the results of your formulas. Add a scenario, identify its changing cells, enter its assumptions, and use the Scenario Summary command to create a comparison report.

What scenario management does

Scenario Manager is Excel’s built-in way to keep named sets of assumptions in a workbook. A scenario might represent a low, expected, or high estimate, such as different sales volumes and costs. Each scenario supplies values for selected input cells; formulas that depend on those cells calculate the corresponding results.

It is useful when you want to compare a small number of coherent cases. Rather than repeatedly typing assumptions over your working values and trying to remember the original numbers, you can store and show named versions. Scenario Manager changes the designated input cells when you display a scenario, while the workbook’s formulas calculate the outputs.

It does not forecast which case will happen, assign probabilities, or explain why an outcome changed. Those judgments remain yours. Think of it as a way to organize and display assumptions, not as a prediction or optimization tool.

A simple profit example

Suppose a worksheet models monthly profit. Put units sold in B2, price per unit in B3, variable cost per unit in B4, and fixed costs in B5. Reserve B6 and B7 for calculated outputs. With those inputs in B2:B5, use this formula in B6 to calculate revenue:

=B2*B3

With the same layout, use this formula in B7 to calculate profit:

=B2*(B3-B4)-B5

For a base case of 1,000 units, a $50 price, a $30 variable cost, and $12,000 in fixed costs, the profit formula returns $8,000: 1,000 × ($50 − $30) − $12,000. Create a “Downside” case with 800 units, a $45 price, a $32 variable cost, and $13,000 in fixed costs. Its profit is −$2,600. An “Upside” case with 1,200 units, a $52 price, a $29 variable cost, and $12,000 in fixed costs returns $15,600.

The example makes the purpose clear: all three cases use the same calculation, but the assumptions differ. Use input cells for the changing assumptions and keep formulas in separate output cells. If you put a formula in a changing cell, showing a scenario can replace that cell’s contents with a stored value.

How to create and show scenarios

First, make sure your worksheet has clearly labeled input cells and formulas for the results you want to evaluate. For the example above, the four input cells are B2:B5, and the result cell is B7. Then create a scenario in Excel for Microsoft 365 on Windows:

  1. Open the worksheet containing the model and select Data > What-If Analysis > Scenario Manager.
  2. Select Add. Enter a descriptive scenario name, such as “Downside.”
  3. In Changing cells, specify the input cells that this case will vary, such as B2:B5. You can select cells in the worksheet while setting up the scenario.
  4. Select OK. In the Scenario Values dialog, enter the values for each changing cell in the displayed order, then select OK.
  5. Repeat the process for other cases. In Scenario Manager, select a scenario and choose Show to apply its values to the worksheet.

Use names that describe the assumptions, not just labels such as “Scenario 1.” If you have many changing cells, make their worksheet labels easy to match to the order in the Scenario Values dialog. A wrong value entered on the wrong row can produce a plausible-looking but misleading result.

To preserve your original assumptions, record them as a named “Base” or “Current plan” scenario before displaying alternatives. You can also save a copy of the workbook first. Showing a scenario replaces the current contents of its changing cells; having a baseline scenario makes it straightforward to restore those assumptions.

Creating a scenario summary report

Scenario Manager can generate a report that presents several saved cases together. Open Scenario Manager, select Summary, choose Scenario summary, and specify the result cell or cells whose calculated values you want to compare. Excel creates a new worksheet showing the scenarios, their changing values, and the selected results.

For the profit example, select B7 as the result cell. The summary can show the assumptions for each case alongside the resulting profit, making it easier to compare the downside, base, and upside outcomes without switching between them one at a time. Give result cells meaningful labels so the report is understandable when separated from the model.

A Scenario Summary is a report, not a live dashboard that automatically recalculates every time you edit assumptions. If you change the model or scenario definitions, create a fresh summary when you need an updated comparison. Scenario Manager also offers a Scenario PivotTable report option for organizing scenario data in a PivotTable-style report; choose it when you need that layout rather than a straightforward summary.

Choosing changing cells and result cells

Changing cells should be the assumptions that define a case. They may include volume, price, staffing, or cost assumptions. Result cells should be formula cells that measure what you care about, such as profit, cash required, or total hours. Choosing only the inputs that truly vary keeps each case easier to enter, review, and explain.

Excel allows up to 32 changing cells in a scenario. That limit can make a detailed model cumbersome if dozens of assumptions need to vary together. Consider whether some inputs can be consolidated or whether another approach is better. Keep inputs in a consistent order and use descriptive worksheet labels; the scenario values are much easier to audit when a reviewer can trace each number back to its assumption.

For a useful comparison, include results that answer the decision you are considering, not every formula in the workbook. A sales plan might need both profit and units sold, while a staffing model might need labor cost and capacity. Make sure the selected result cells actually depend on the changing cells; otherwise, their values will be identical across cases even when other parts of the model change.

Scenario Manager compared with other Excel tools

  • Scenario Manager: Best for a short list of named cases, each with a set of assumptions. It lets you show one case at a time or create a summary report.
  • Data Tables: Better for testing one or two input variables across a grid of values, such as profit at many combinations of price and volume. A Data Table is designed for systematic sensitivity analysis, rather than saving a few named business cases.
  • Goal Seek: Useful when you know a target formula result and want Excel to find the input value that produces it. It works backward for one changing input; it is not a tool for comparing a collection of named assumptions.
  • Solver: Suited to finding an optimum subject to constraints, such as maximizing profit while respecting limits. Solver is an add-in and is different from Scenario Manager’s descriptive comparison of cases.
  • Separate worksheets or formulas: May be simpler for a large or highly customized model, especially if you want several cases visible side by side. The trade-off is that duplicated formulas or model layouts can become harder to maintain consistently.

These tools answer different questions. Scenario Manager asks, “What do the results look like under these selected assumptions?” Goal Seek asks, “What input reaches this target?” A Data Table asks, “How do results vary across these input values?” Choose based on the question, rather than treating all What-If Analysis tools as interchangeable.

Common problems and practical safeguards

  • The wrong result appears: Check that the selected changing cells are the intended inputs and that values were entered in the matching order. Confirm that the result formula references those inputs.
  • The worksheet no longer shows the original numbers: Showing a scenario applies its values to the changing cells. Show your saved baseline scenario or restore the original assumptions manually.
  • The summary leaves out an important measure: Recreate the report and select the relevant formula cells as result cells. A report cannot compare a result you did not include.
  • Scenario names are hard to interpret: Use clear labels such as “Higher material costs” or “Sales target met.” Add notes on what distinguishes each case if the assumptions are not self-explanatory.
  • Too many assumptions need to change: Remember the 32-changing-cell limit. Simplify the model inputs or use a different analysis method if the scenarios are too complex to manage reliably.

Before relying on a comparison, inspect the underlying formulas and units. A price entered as 50 in one case and 0.50 in another, or monthly costs compared with annual revenue, can invalidate an otherwise tidy report. If other people use the workbook, label units, document important assumptions, and keep a clean baseline.

Availability and version notes

The steps here are for the desktop version of Excel for Microsoft 365 on Windows. In desktop Excel for Mac, Scenario Manager is available through the Data tab’s What-If Analysis tools, though ribbon placement can vary with the Excel version or ribbon layout. Excel for the web does not provide the full desktop Scenario Manager workflow for creating and managing scenarios; open the workbook in the desktop app for these steps.

If a command is not visible, check that you are in the desktop app and look on the Data tab for What-If Analysis. A customized or simplified ribbon can change where commands appear, but the feature is part of Excel’s What-If Analysis tools. Scenario Manager is not a worksheet function: there is no formula you enter in a cell to create or display a scenario.

Frequently Asked Questions

How many changing cells can an Excel scenario have?
Excel supports up to 32 changing cells in a scenario. If a case requires more, reduce the number of independently varying inputs or consider another modeling approach.

Can I compare scenarios side by side?
Yes. Use the Summary command in Scenario Manager to create a Scenario Summary report and select the result cells to compare. To display one scenario directly on the model sheet, select it and choose Show.

Does Scenario Manager change my formulas?
It is intended to change the values in the changing cells, while formulas that depend on those inputs recalculate. Keep formula cells out of the changing-cell list so a scenario does not replace a calculation with a stored assumption.

Can I use Scenario Manager in Excel for the web?
The full Scenario Manager creation and management workflow is a desktop Excel feature. Open the workbook in Excel for Windows or Mac to create and work with scenarios.

What is the difference between a scenario and a Data Table?
A scenario stores a named set of assumptions, such as “Downside” or “Upside.” A Data Table evaluates a formula across a grid of values for one or two inputs, making it more suitable for broad sensitivity analysis.

Related

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.