Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Creating Scenarios.
Written by Allen Wyatt (last updated January 9, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
You already know that Excel provides the ability to play "what if" with your worksheets. As an adjunct to this capability, Excel provides the Scenario Manager, which allows you to save different data scenarios. Using this tool, you can save different variables for your sheet and call them up quickly and easily.
As an example, let's suppose that you work for Talbot Industries, and you have been charged with developing a profitability analysis for a new product—the Potato Chip Peeler. You develop your sheet, taking into account all the appropriate information. However, you know that it is prudent at your company to actually prepare three forecasts. The first would be the worst-case scenario, the second is the most-likely scenario, and the final is the best-case scenario.
To save a scenario, follow these steps:
Figure 1. The Scenario Manager dialog box.
Figure 2. The Add Scenario dialog box.
Once you have a scenario saved, you can make changes to your workbook and save your changed figures under a different scenario.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8567) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Creating Scenarios.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Once a scenario is defined and saved, you can later revisit the values you created for the scenario and modify them. ...
Discover MoreWhen you create and save scenarios in a workbook, you can later pull them out and display them. Here's how to do it.
Discover MoreIf you've defined a variety of scenarios for your workbook, Excel can provide a handy way to compare the effects of those ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-01-24 00:45:46
Tomek
There are many uses of scenarios that will let you have just one spreadsheet, instead of several versions than need maintenance.
One that may not be obvious, but I found very useful, was to create different Language versions of the same calculation sheet (in my case to be used in English, French and Spanish speaking locations of our company). In that case all labels and text cells were included in the scenarios and were switched to the preferred language when the user selected the appropriate scenario (I automated that part by using a drop-down list).
Unluckily, scenarios have sheet scope, not workbook scope, but how to overcome this is another story.
2023-01-23 15:26:12
Tomek
There are many uses of scenarios that will let you have just one spreadsheet, instead of several versions than need maintenance.
One that may not be obvious, but I found very useful, was to create different Language versions of the same calculation sheet (in my case to be used in English, French and Spanish speaking locations of our company). In that case all labels and text cells were included in the scenarios and were switched to the preferred language when the user selected the appropriate scenario (I automated that part by using a drop-down list).
Unluckily, scenarios have sheet scope, not workbook scope, but how to overcome this is another story.
2023-01-23 15:06:01
Tomek
Scenarios are a very useful tool when creating versions of the same spreadsheet, but you have to understand how they work to avoid pitfalls. Here are two of the rules that I learned hard way, and I now follow when using scenarios:
Rule 1
As changing cells select only those that contain constants. Any formulas will be converted to constants when the scenario is displayed, and the calculations may be lost. Instead, include all ultimate precedent cells as the changing cells; when the values of those change the resulting calculations will be updated as well.
Hint: I colour-code all changing cells to let the user know that these are "Input Cells" (I use light green background).
Rule 2:
If creating several related scenarios, consider including the same changing cells in all of them, even if some of them do not change in most of your scenarios. This will prevent forgetting those cells when switching from an atypical to typical scenario.
There are more tips on scenarios on this site as well as elsewhere on the internet so check them too.
2023-01-23 14:31:56
Tomek
Gregg:
You cannot really toggle the scenario, what you can do is only display a saved scenario. When you do this, whatever was in the changing cells of the selected Scenario will be replaced (overwritten) by the values saved within the scenario. While you cannot un-display the scenario, you can use undo to revert to previous status, i.e, before displaying the scenario.
If you need certain set of data to revert to after displaying other scenarios, you need to save that set as another scenario, may-be naming it a Stating Scenario.
If by toggle off the scenario you meant "How do I edit my data after displaying a scenario?" you can simply do that; what displaying a scenario means is that the values in the changing cells are recalled from the scenario, but you can keep editing them as well as the rest of the sheet as needed.
I will post another comment with hints on how to design a scenario and what to avoid.
2023-01-22 14:29:04
Gregg W. Tulley
Once I have created and saved a Scenario using the Scenario Manager in Excel 2019, how do I toggle off the Scenario so that I may revert back to the original workbook, with the ability to toggle that Scenario when desired?
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments