Excel Scenario Manager: Compare What-If Cases Side by Side

Scenario Manager stores named sets of input values — Best Case, Worst Case, Base Case — and swaps them into your model with one click, then prints a summary report comparing every scenario's results side by side. It is the right tool when you have a handful of discrete cases built from several inputs at once; it is the wrong tool when you want to sweep one or two inputs across many values, or when your model has more than 32 changing cells.

How do I create scenarios in Excel?

Build your model with clearly separated input cells first, then register each case. Suppose B1 is units sold, B2 is price, B3 is unit cost, and B6 holds =B1*(B2-B3)-15000 (profit):

Data → What-If Analysis → Scenario Manager → Add...
  Scenario name:   Base Case
  Changing cells:  B1:B3
  → OK, then enter values: 5000, 29, 11

Repeat Add... for:
  Best Case:   6500, 32, 10
  Worst Case:  3500, 26, 13

Select a scenario and click Show to write its values into B1:B3 — the whole workbook recalculates as if that case were reality. Note that Show overwrites the input cells; define your current values as a "Base Case" scenario first so you can always get back.

How do I build a scenario summary report?

Click Summary... in the Scenario Manager dialog, choose "Scenario summary", and point "Result cells" at the outputs you care about (e.g. B6, or several cells separated by commas). Excel inserts a new formatted sheet showing every scenario's inputs and the resulting outputs in columns — the side-by-side comparison that stakeholders actually want to see.

Summary... → ☑ Scenario summary
  Result cells: B6

→ New "Scenario Summary" sheet:
             Current  Base    Best    Worst
  B1 units     5000    5000    6500    3500
  B2 price       29      29      32      26
  B3 cost        11      11      10      13
  B6 profit   75000   75000  128000   30500

Two practical notes: the report is a static snapshot — change the model and you must regenerate it — and it labels rows with cell addresses unless you name the cells. Define names (Formulas → Define Name) for every changing and result cell before running Summary, and the report becomes genuinely readable ("Units_Sold" instead of "$B$1"). The "Scenario PivotTable report" option is rarely worth it unless you are merging scenarios from multiple users.

What is the 32-cell limit in Scenario Manager?

Each scenario can store values for at most 32 changing cells — Excel refuses more, with a "too many changing cells" error. That cap is per scenario and cannot be raised. In practice it is a design signal rather than an obstacle: if a "case" needs more than 32 inputs, your model's assumptions have sprawled. Consolidate related inputs (one growth rate instead of twelve monthly ones), or restructure so scenarios only vary the true decision drivers. If you genuinely need large alternative datasets, a parameter sheet (below) or Power Query source-switching handles it better than Scenario Manager ever will.

Scenario Manager vs data tables — which one when?

They answer different shapes of question. Scenario Manager compares a few named, discrete cases where many inputs change together. A data table sweeps one or two inputs across a continuous range of values and shows the output for every combination, live. If you are asking "best vs worst vs expected", use scenarios; if you are asking "profit at every price from $20 to $40", use a data table. Data tables recalculate automatically; scenario summaries do not. And if the question is the inverse — "what input gets me to a target output?" — that is Goal Seek's job. All three tools live under the same menu, mapped out in our What-If Analysis overview.

When does a parameter sheet beat Scenario Manager?

Scenario values are hidden inside a dialog: invisible on the sheet, awkward to audit, easy to overwrite with a stray Show, and unfriendly to version control or review. A parameter sheet keeps every case visible: one column per scenario, one row per assumption, and a chooser cell that pulls the active column into the model.

' Params sheet: B=Base, C=Best, D=Worst; row per input
' E1 holds the chooser: 1, 2 or 3 (or a dropdown)
' Model pulls inputs with:
=CHOOSE($E$1, B2, C2, D2)
' or, with scenario names in B1:D1:
=INDEX(B2:D2, MATCH($E$1, $B$1:$D$1, 0))

Prefer the parameter sheet when scenarios must be reviewed by others, when you need more than 32 inputs, or when the model is long-lived. Prefer Scenario Manager for quick, personal comparisons and its auto-generated summary report — it is faster to set up for a one-off analysis.