The Scenario Manager can keep a set of input values for specific cells, and allows you to switch between them.This can be very helpful if you have a lot of input combinations to test against a model. In this guide, we’re going to show you how to use Scenario Manager in Excel.
Sample Data
In this example, we have a data table and a simple formula which uses the SUMIFS function. The formula returns the sum of values under the Total column, based on criteria in cells O3 and O4 which are tested under the Type and Sp. Atk columns, respectively.
Let's say, we want to find what our formula returns for different input sets. For example, our default inputs are Type = FIRE* and Sp. Atk <100. A second scenario is Type = WATER and Sp. Atk 70.
Using Scenario Manager to test different input sets
Adding scenarios
- (Optional) Select your input cells.
- Follow Data > What-If Analysis > Scenario Manager path in the Ribbon.
- Click the Add button in the Scenario Manager dialog.
- In the Add Scenario dialog, enter a friendly name for Scenario Name.
- Changing cells are the inputs you want to save.
- (Optional) Add a Comment.
- (Optional) Set Protection.
- Select Prevent Changes to prevent editing the scenario if the worksheet is protected.
- Select Hidden to prevent displaying the scenario if the worksheet is protected.
- Click OK to go to the next dialog: Scenario Values.
- Enter the values you want to see in the scenario to be saved.
- Clicking OK saves your scenario.
- Use the Add button in Scenario Manager to add more scenarios.
Testing scenarios
Once the scenarios are ready, use either the Show button or double-click on the scenario names to see the results in the workbook.
To see all scenarios in one place, use the Summary button. The tool will prompt you to select a report type:
- Scenario summary
- Scenario PivotTable report
Remember to select the formula cells, which are affected by input cells, for Result cells.
Clicking OK creates a new worksheet with your selected report.
Scenario Summary:
Scenario PivotTable report:
