To run Scenario Manager in Excel, open the Data tab, choose What-If Analysis > Scenario Manager, add scenarios using your model’s input cells, and then show a scenario or create a summary report. Scenario Manager lets you compare how different sets of assumptions affect formula results without manually replacing your inputs each time. Use the desktop version of Excel for Microsoft 365 on Windows for the steps below; the feature is not available in Excel for the web.
What You’ll Need
- A desktop copy of Excel and a workbook with a working model.
- Input cells for assumptions you want to vary, such as price, units, or costs.
- One or more formula cells that calculate the results you want to compare.
Make a copy of your workbook before changing an important model. Scenario Manager stores alternative values for input cells, but it does not check whether your assumptions are sensible or whether the model is correct.
Step-by-Step
-
Set up a small, clear model. Put each assumption in its own cell and label it so you can identify it later. For example, use A2:A5 for labels Selling price, Units, Variable cost per unit, and Fixed costs, with the corresponding values in B2:B5. Put a result label such as Profit in A6. With selling price in B2, units in B3, variable cost per unit in B4, and fixed costs in B5, enter
=B2*B3-B4*B3-B5in B6. At 25, 100, 12, and 700 respectively, the formula returns 600: 25 times 100, less 12 times 100, less 700. -
Check that the model responds to its inputs. Temporarily change one input cell, such as B2, and confirm that the result in B6 changes. Undo the edit or restore the original value before creating scenarios. Scenario Manager changes the cells you specify and recalculates dependent formulas; it cannot repair a formula that references the wrong cells or a result that was typed in as a fixed number.
-
Open Scenario Manager. Select the worksheet containing the model, then choose Data > What-If Analysis > Scenario Manager on the Windows ribbon. In the Scenario Manager dialog, choose Add. If the command is missing, check the troubleshooting section below; Excel for the web does not provide this desktop feature. Excel for Mac has Scenario Manager in the Data tab’s What-If Analysis menu in supported desktop versions, though ribbon placement can vary.
-
Add the first scenario. In the Add Scenario dialog, type a descriptive name, such as Base case. In the Changing cells box, select B2:B5, or enter the references for the input cells you intend to vary. Do not include the profit formula in B6. The cells must be part of the model you want Scenario Manager to change. You can select non-adjacent input cells, but a short, well-organized range is easier to review.
-
Choose how Excel displays changes. Select OK to open the Scenario Values dialog. The dialog lists the selected cells in order, with their current values. Confirm that each value matches the label you expect: B2 should be the selling price, B3 the number of units, B4 the variable cost, and B5 the fixed costs. Type the assumptions for this scenario in that same order, then choose OK. If a comment box appears, you can add context such as the source or date for the estimates.
-
Add more scenarios. In Scenario Manager, choose Add again and give each alternative a distinct name, such as Conservative or Growth. Keep the same changing-cell range if you want a direct comparison. For the example model, a Conservative case could use 23, 90, 12, and 700. A Growth case could use 27, 120, 13, and 800. Enter each set in the order shown in the Scenario Values dialog. These values return profits of 290 and 880 respectively, using the same formula. Scenario Manager supports up to 32 changing cells per scenario.
-
Display a scenario on the worksheet. In the Scenario Manager dialog, select a scenario name and choose Show. Excel places that scenario’s assumptions into the changing cells and recalculates the model, so B6 displays its result. Showing a different scenario replaces those displayed input values with the other scenario’s values. The scenario is saved in the manager, but the worksheet displays only one scenario’s assumptions at a time.
-
Create a comparison report. In Scenario Manager, choose Summary. Select Scenario summary, then specify B6 as the Result cells range. Include only the formula cells whose outputs you want to compare; do not select the input cells as results. Choose OK. Excel creates a new worksheet listing the scenarios, their changing values, and the selected result. Give the new sheet a useful name, and check that the listed result matches the model for each case.
-
Return the model to the assumptions you want to keep. A summary report does not automatically restore your preferred live inputs. To reset the worksheet, open Scenario Manager, select the intended scenario, and choose Show. Alternatively, type your original assumptions back into the changing cells. Save the workbook after confirming that the displayed inputs and formula results are the ones you want to retain.
Common Mistakes
-
Selecting result cells as changing cells. The Changing cells range should contain assumptions, not formula outputs. If you include B6 in the example, Excel may overwrite the profit formula with a scenario value. Keep input and result cells clearly separated.
-
Entering values in the wrong order. The Scenario Values dialog follows the order of the changing-cell references, not the visual order of your labels or the order in which you thought of the assumptions. Verify each cell reference and value before saving.
-
Using formulas that do not depend on the selected inputs. A scenario can change inputs correctly while the displayed result stays the same if the result formula points elsewhere, is hard-coded, or uses different cells. Check the formula references before assuming Scenario Manager failed.
-
Expecting the summary to update live. A Scenario Summary is a report worksheet created from the values and results at the time you made it. If you later edit the model or scenarios, create a new summary when you need a current report.
-
Forgetting which scenario is currently displayed. Showing a scenario changes the live input cells. Before saving or sharing the workbook, check that the worksheet is displaying the intended case rather than the last case you happened to inspect.
Tips
-
Use short, meaningful names that distinguish the assumptions, such as Base case, Lower demand, or Higher costs. Avoid names like Scenario 1 if you will revisit the workbook later.
-
Keep scenario assumptions documented. Add a comment in the Add Scenario dialog when useful, and include units in worksheet labels, such as Price per item or Units per month. That makes it easier to interpret both the live model and the summary sheet.
-
Start with a base case that matches the values already in your model. This gives you a reference point and makes it easier to spot an input entered in the wrong cell.
-
Choose result cells that answer a practical question. A model may have several outputs, such as profit, cash required, or units remaining. Select the formula results that help distinguish the scenarios, rather than adding unrelated cells to the report.
-
For a quick view, use Show to display scenarios one at a time. For a saved side-by-side comparison, use Summary. Scenario Manager is most helpful when you have a small number of clearly defined assumptions; for many combinations of inputs, a data table or a separate analysis may be easier to maintain.
Troubleshooting
Scenario Manager is not in the menu: Confirm you are using the Excel desktop app, not Excel for the web. In Windows, look on the Data tab under What-If Analysis. In Excel for Mac, check the Data tab for What-If Analysis; the menu layout can differ by version. If you are using a managed work device, an organization’s setup or an older Excel version may affect what is available.
The result does not change when you choose Show: First check that the selected changing cells are the actual inputs used by the result formulas. Then inspect the formulas and confirm that calculation is set to automatic by choosing Formulas > Calculation Options > Automatic. If the model uses inputs or calculations on other sheets, verify that the formulas reference the intended cells and that the scenario changes the correct inputs.
The wrong numbers appear in the worksheet: Open Scenario Manager, select the scenario, choose Edit, and check the Changing cells range and values. Compare the list with the worksheet labels, one cell at a time. If the scenario is correct but the model is left showing the wrong case, select the desired scenario and choose Show to restore its inputs.
The summary is missing a result or shows an unexpected value: Reopen the summary process and select the formula cell or cells you want in the Result cells box. Confirm that the result formula is working before creating the report. If you changed the model after generating a summary, create a fresh report; the existing summary does not refresh itself.
Excel will not accept the changing-cell selection: Reduce the selection to the actual input cells and keep within Scenario Manager’s limit of 32 changing cells per scenario. Avoid selecting a large block that contains blank cells, labels, formulas, or unrelated values. If the model has more than 32 assumptions to vary, consider splitting the analysis into smaller, meaningful groups.
Frequently Asked Questions
What does Scenario Manager do in Excel?
It saves different sets of values for selected input cells. You can display a saved set on the worksheet or create a report comparing how those assumptions affect selected formula results.
Can I compare scenarios side by side?
Yes. Choose Data > What-If Analysis > Scenario Manager > Summary, select Scenario summary, and choose the output formula cells as Result cells. Excel creates a worksheet listing the scenario assumptions and results together.
Does Scenario Manager change my formulas?
It is designed to change the values in the cells you specify, not to create or rewrite your model formulas. That is why you should select input cells and keep formula outputs out of the Changing cells range.
Can I use Scenario Manager in Excel for the web?
No. Use the desktop version of Excel to create, edit, show, or summarize scenarios. If someone sends you a workbook containing a report, you may be able to view its worksheet in the web app, but you need desktop Excel to manage Scenario Manager scenarios.
How do I delete or change a saved scenario?
Open Scenario Manager, select the scenario, and choose Edit to change its name, changing cells, or values. Choose Delete to remove it. Deleting a saved scenario does not itself erase the worksheet’s model formulas.
Why does my workbook still show one scenario after I make a summary?
The live worksheet can display only one selected set of scenario inputs at a time. The summary worksheet is where the saved alternatives are compared together. Use Show to switch the live worksheet to a different scenario.
Related
- Fixing a Corrupted Excel File: Recovery Steps
- Excel File Extension Corrupted? Causes and Safe Fixes
- Excel File Contains Corrupted Data? Ways to Recover It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Find a Range in Google Sheets: Select Cells FastTo find and select a range in Google Sheets, click the Name box to the left of the…
- How to Delete a Blank Page in Microsoft WordTo delete a blank page in Microsoft Word, show formatting marks, find the paragraph mark or break that…
- Excel Remove Duplicates Based on Two ColumnsTo remove duplicate records based on two columns in Excel for Microsoft 365 on Windows, select the entire…
- How to Add Page Numbers in ExcelTo add page numbers in Excel for Microsoft 365 on Windows, open the worksheet in Page Layout view,…
- How to Add Page Number X of Y in Word Documents EasilyTo show a running total such as “Page 3 of 12” in Microsoft Word, open Insert > Page…
- Debt Payoff Spreadsheet Template: Free Excel GuideYes—a debt payoff spreadsheet template can be free: you can build one in Excel with a simple table,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.