Scenario Manager in Excel stores several named sets of input values for the same cells, lets you switch between them instantly and produces a Scenario Summary report that compares every outcome side by side. In this lesson you will learn how to set up a worksheet for scenario analysis, name the key cells, add scenarios such as different sales quantities, show them, and generate and read the summary report.
What Scenario Manager does and when to use it
Scenario Manager is one of the What-If Analysis tools on the Data tab. A scenario is simply a saved list of values for up to 32 changing cells. Showing a scenario writes those values into the cells so every dependent formula recalculates; showing another replaces them. Because the sets are stored inside the workbook, you can present Best, Base and Worst cases to a manager without maintaining three copies of the model. Use it when inputs take a few discrete alternatives; use Goal Seek for a single target and a Data Table for a continuous sweep.
Set up the worksheet
The example is a small sales model. Column A holds the labels, column B the values and column C explains each formula. Price and Qty are inputs; Total Revenue, Total Costs and Profit are formulas that reference them.
Three habits make scenario analysis reliable: label every input clearly, keep the inputs together in a logical order, and reference the input cells in every formula rather than typing numbers into the formulas.
Name the input and output cells
Named cells make the Scenario Summary readable, showing “Qty” and “Profit” instead of $B$3 and $B$8.
- Go to the Formulas tab and click Name Manager, then New.
- Type Qty as the Name and
=Sheet1!$B$3in Refers to, then click OK. - Repeat for Profit pointing to $B$8. A faster route is to select the cell and type the name in the Name Box left of the formula bar.
How to add scenarios
- Go to Data > What-If Analysis > Scenario Manager. The dialog opens empty the first time.
- Click Add. In the Add Scenario dialog type a name such as Qty 200, set Changing cells to B3 (or the name Qty), add a comment if useful and click OK. Tick Prevent changes if the scenario must not be edited later.
- In the Scenario Values dialog type 200 for Qty and click OK (or Add to go straight to the next scenario).
- Repeat for quantities of 300, 400 and 500. The dialog now lists four scenarios; select one and click Show to write its value into B3 and watch Profit change.
Create the Scenario Summary report
- Click Summary in the Scenario Manager.
- Choose Scenario summary as the report type. Scenario PivotTable report is the alternative when several people have contributed scenarios.
- In Result cells select B8, the Profit cell (several result cells can be listed with commas), and click OK.
Excel inserts a new sheet named Scenario Summary.
How to read the Scenario Summary
The left column lists the changing cells (Qty) and the result cells (Profit); each scenario is a column, with Current Values first showing the inputs at the moment the report was built. Read across the Profit row to see which quantity delivers the best result, and use the outline buttons to collapse the detail. Because named cells were used, the labels are meaningful. The report is a static snapshot: if the model changes, delete the sheet and run Summary again.
Tips and common mistakes
- Keep a Base Case scenario holding the original inputs so you can restore them after showing others.
- Scenarios belong to a sheet. Changing cells must be on the active sheet; use Merge to combine scenarios from other sheets or workbooks.
- Editing cells does not update scenarios. Use Edit in the Scenario Manager to change stored values.
- Protect the structure by ticking Prevent changes and protecting the sheet when distributing the model.
- Result cells must be formulas that depend on the changing cells, otherwise every column shows the same number.
Practice and real-world use
Add Price as a second changing cell and create Best, Base and Worst scenarios that change both Price and Qty, then produce a summary with Revenue and Profit as result cells. Budget versions, pricing options and hiring plans are the typical business uses of Scenario Manager.
Click here to download the practice file.
Watch the step-by-step video tutorial
Related lessons
Frequently asked questions
How many changing cells can a scenario have?
Up to 32 changing cells per scenario. All of them must be on the same worksheet, and they should contain values, not formulas, because showing a scenario overwrites them.
How do I edit or delete a scenario?
Open Data > What-If Analysis > Scenario Manager, select the scenario and click Edit to change its name, cells or values, or Delete to remove it. Re-run Summary afterwards to refresh the report.
Why does my Scenario Summary show the same result for every scenario?
The result cell does not depend on the changing cells, usually because a number was typed into the formula instead of referencing the input cell. Fix the formula so it points at the changing cell.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.