You can use Excel Scenarios to store several versions of the data in a worksheet. To make Scenarios easier to use, see how to use a macro to automatically show a Scenario, when you select its name from a drop down list on the worksheet.
Tip: To show Scenarios without a macro, see the steps to put the Scenario command on the Excel Ribbon.
In this example, there are two scenarios, with budget amounts for the Marketing Department, and the Finance Department.
For each scenario, there are inputs for 3 changing cells:
This example is based on the Excel Scenarios created in the Excel Scenarios Create and Show tutorial. The video in the next section shows how those scenarios were created, if you'd like to see the steps.
Video: Set Up Excel Scenarios
To see the steps for setting up the Scenarios, and showing them manually, please watch this short video tutorial.
Written steps are in the Excel Scenarios Create and Show tutorial.
View Scenario - Manually
To view a Scenario, you can use built-in commands on the Excel Ribbon. Those steps are listed below, and in the next section, you'll see how make it easier, by using a Scenario macro.
To change to a different scenario, follow these steps:
To show a Scenario, follow the steps below:
View Scenario - Macro
To make it easier to switch between Scenarios, you can create a drop down list of Scenarios, and use event code to show the selected Scenario.
In the sections below, there are instructions for the four steps:
Step 1: Create a list of Scenarios
Follow these steps to add a new worksheet, and make a list of Scenarios
Step 2) Name list of Scenarios
The list will be easier to maintain if it's a dynamic named range. With this type of named range, it will adjust automatically if you add or remove Scenario names.
To create the named range follow these steps:
5. Click OK
Step 3) Create Drop Down list
The next step is to create a drop down list of Scenarios on the budget sheet. Then, instead of using the Scenario Manager, you'll be able to select a Scenario name right on the worksheet.
To create the drop down list, follow these steps
Now, when you click on cell B1, a drop down arrow appears at the right side.
You can click the arrow, and select a Scenario name, but nothing will change in the Budget values yet. You'll add the VBA code in the next section.
The final step is to add a bit of code to the Budget sheet's code module. This code will update the changing cells on the worksheet, to show the values from the scenario that you selected.
Follow these steps, to open the code module and to enter the code.
If Target.Address = "$B$1" Then ActiveSheet.Scenarios(Target.Value).Show End If
Test Show Scenario Code
Save the Workbook
Now that you've added VBA code to the workbook, you'll need to save the file in a macro-enabled format. To do that, follow these steps:
Note: When opening the workbook, you will have to enable macros in order to use this code.
Show Scenario Macro: Click this link to get the completed Excel Automatically Show Scenarios Macro workbook. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the code.
Last updated: August 19, 2022 8:54 PM