How to Use Excel’s Scenario Manager (Step-By-Step)
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Have you been in a situation when you wanted to have different values in the same cell?
Chances are, you created another sheet for it or worse, created a new file to compare your different scenarios.
But did you know that you can Excel scenario manager for that? 💡
In this article, let’s discuss what the scenario manager is in Excel and how to use it.
Kasper Langmann, Co-founder of SpreadsheetoWhat is the scenario manager in Excel?
The scenario manager is one of the three tools, together with ‘Goal Seek’ and ‘Data Table’, included in the ‘What-If Analysis’ toolset in Excel.
Using the scenario manager enables you to store and show multiple versions (or scenarios) of your data, in the same cells.
Kasper Langmann, Co-founder of SpreadsheetoThis is useful for a lot of things such as in:
- Marketing
- Finance
- Budgeting
- Sensitivity analysis
In a way, the scenario manager has similar uses to data tables as both will allow you to project possible results using a set of values.
As you will see below, creating and showing different scenarios is as easy as using different sheets. 😊
How to add a scenario in Excel?
For this tutorial, let’s use a simple set so you would be able to see the difference between different scenarios.
In this case, we would want to change all the “A” variables into “B”:
- Scenario B
- Primary value B
- Secondary value B
- Total B
To start, select the cells with the values you want to change.
Note that you can only select a maximum of 32 changing cells.
In our example, it’s B2, B4, B5, and B7.
Then, follow these steps:
- Go to the ‘Data’ tab
- Click the ‘What-If Analysis’ button
- Select ‘Scenario Manager…’
On the ‘Scenario Manager’ window, click on the ‘Add…’ button.
On the next window, give your scenario a title or a name. (Let’s name it “Scenario B”.)
The reason why the first step in this is selecting the cells is so that you don’t have to do it in this window. But if you want, you can by entering the cells on the ‘Changing cells’ field or selecting the cells yourself by clicking on the up arrow button.
Kasper Langmann, Co-founder of SpreadsheetoIf you like, you could also protect your scenarios by preventing any changes or hiding it from others.
Click on the ‘OK’ button once you’re ready to proceed.
After that, a new window will appear where you can enter the values for your new scenario.
Click the ‘OK’ button to proceed.
That’s it! You just created a new scenario in Excel! 👍
As you can see, you can either add, delete, edit, merge, or summarize your scenarios.
Summarizing your scenarios would be creating a data table or a pivot table report of all your scenarios.
How to show a scenario?
To show a scenario that you have created, all you have to do is go back to the ‘Scenario Manager’ window (if you closed it).
Then, select the scenario and click on the ‘Show’ button.
Immediately, you will now see that all the changing cells have been replaced with their values on the scenario that you have created.
Easy, right!? 😉
Wrapping things up…
Although we have used a simple example, you could apply the scenario manager into complex spreadsheets like in budgeting or in marketing.
The idea behind this tool is simple — to allow you to create and show different scenarios to give you a comparative overview.
If you would like to test it out yourself, feel free to download the exercise file included in this tutorial and simply follow the steps outlined here. 😊
Kasper Langmann, Co-founder of Spreadsheeto