Home > Macros > > Scenarios

Macro to Show Excel Scenario

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.

Create Drop Down List of Scenarios

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:

• B1 - Department name
• B3 - Sales amount
• B4 - Expenses amount

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:

1. On the Excel Ribbon, click the Data tab
2. Next, at the right end of the tab, in the Forecast group, click What If Analysis
3. In the drop down list of tools, click Scenario Manager.

To show a Scenario, follow the steps below:

1. The Scenario Manager dialog box opens, showing a list of scenarios in the workbook
2. In the list of Scenarios, click on one of the Scenario names, to select it
3. At the bottom of the dialog box, click the Show button
   • Selected Scenario's values immediately appear on the worksheet
   • (optional) Select a different Scenario in list, and click Show, to see its values
4. After you select and show the Scenario that you want to use, click the Close button, to close the Scenario Manager dialog box.

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:

1. Create a list of scenario names
2. Name that list
3. Create drop down list on worksheet, using data validation
4. Add a small amount of code, to automate the scenario selection

Step 1: Create a list of Scenarios

Follow these steps to add a new worksheet, and make a list of Scenarios

1. In the row of sheet tabs, click the + button (New sheet), to add a new worksheet
2. Double-click on the new sheet's tab, and rename it as Lists  
3. In cell A1, type a heading for the list - Scenarios
4. Make the heading cell Bold font
5. In cells A2 and A3, type the Scenario names - Finance and Marketing

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:

1. On the Lists worksheet, select cell A1
2. On the Excel Ribbon, click the Formula tab
3. In the New Name dialog box, type this one-word name for the list:
• ScenarioList
4. In the Scope drop down, select Workbook
5. The Comment box can be left blank
6. For Refers to, type the following formula:

• =OFFSET(Lists!$A$1,1,0, COUNTA(Lists!$A:$A)-1,1)

  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

1. On the Budget worksheet, select cell B1
2. On the Excel Ribbon, go to the Data tab
3. In the Data Tools group, click the Data Validation command
4. In the Data Validation dialog box, go to the Settings tab
5. From the Allow dropdown, choose List
6. In the Source box, type:    =ScenarioList 
7. Click OK

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.

Step 4) Add Event Code

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.

1. Right-click on the Budget sheet tab
2. Choose View Code
3. From the Object dropdown, at the top left, choose Worksheet.
• 
4. From the Procedure dropdown, at the top right, choose Change.

• 
1. Where the cursor is flashing, enter the following code:

If Target.Address = "$B$1" Then
     ActiveSheet.Scenarios(Target.Value).Show
End If

6. Choose File | Close and Return to Microsoft Excel 

Test Show Scenario Code

1. On the Budget worksheet, select an item from the dropdown list in cell B1.
2. The values from that Scenario will be displayed in the changing cells - B1, B3 and B4.

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:

• At the top left of Excel, click the File button
• In the list at the left, click Save As
• Type a name for the workbook, e.g. ScenarioShowMacro
• In the drop down list for file type, select Excel Macro-Enabled Workbook (*.xlsm)
• (optional) To save the file in a different folder, click the Browse button, and select a new location
• Finally, click the Save button.

Note: When opening the workbook, you will have to enable macros in order to use this code.

Download the Sample File

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.

More Tutorials

Create and Show Scenarios

Scenario Summaries

Macros for Excel Scenarios