Excel Scenarios Create and Show
Use Excel Scenarios to store several versions of the data in a worksheet.
Download the sample file and watch the video or follow the written
instructions
Introduction
With Scenarios in Excel, you can store multiple versions of data,
in the same cells. For example, when preparing a budget, the Marketing
and Finance departments may have different forecasts for sales. You
can store each forecast as a Scenario, print them separately, or compare
them side-by-side.
Note: There is a limit of 32 changing cells in a Scenario
To see the steps for setting up the Scenarios, and showing them,
please watch this short video tutorial. The written instructions are
below the video.
Set up the Excel Scenarios Worksheet
Although Excel scenarios can be complex, a simple example is used
here.
- Delete all sheets except Sheet1
- Rename Sheet1 as Budget
- On the Budget sheet, enter the Marketing budget, as shown
below
- Name the following cells (there are Naming instructions here:
Name
a Range). Naming the cells is not required, but will make it
easier to manage the scenarios, and read the reports:
- Name cell B1 as Dept
- Name cell B3 as Sales
- Name cell B4 as Expenses
- Name cell B6 as Profit
- In cell B6, enter the following formula:
=Sales - Expenses

Create the First Excel Scenario
- On the Ribbon's Data tab, click What If Analysis
- Click Scenario Manager

- In the Scenario Manager, click the Add button
- Type name for the Scenario. For this example, use Marketing.
- Press the Tab key, to move to the Changing cells box
- On the worksheet, select cells B1
- Hold the Ctrl key, and select cells B3:B4
Note: There is a limit of 32 changing cells
- Press the Tab key, to move to the Comment box
- (optional) Enter a comment that describes the scenario.
- Click the OK button

- The Scenario Values dialog box opens, with a box for each changing
cell.
- You could modify these values, but in this example they contain
the values currently on the worksheet, and don't need to be changed.
- Click the OK button, to return to the Scenario Manager.
- Click the Close button, to return to the worksheet

Create the Second Excel Scenario
- To prepare for the Finance scenario, change the values in cells
B1, B3 and B4, as shown below

- On the Ribbon's Data tab, click What If Analysis, then click Scenario
Manager.
- In the Scenario Manager, click the Add button
- Type name for the second Scenario. For this example, use Finance.
- The Changing cells box should show the previous selection -- B1,B3:B4
-- so leave that as is.
- Press the Tab key, to move to the Comment box
- (optional) Enter a comment that describes the scenario.
- Click the OK button
- The Scenario Values dialog box opens, with a box for each changing
cell.
- Click the OK button, to return to the Scenario Manager.
- Click the Close button, to return to the worksheet
Show an Excel Scenario
Once you have created Scenarios, you can show them. In this example,
the Finance scenario is currently visible. To change to a different
scenario:
- On the Ribbon's Data tab, click What If Analysis, then click Scenario
Manager.
- In the list of Scenarios, select Marketing
- Click the Show button
- Click the Close button.

Add Scenario to Excel Ribbon
An easier way to switch between Scenarios, is to add
a command to the Ribbon. Follow these steps, to add a Custom Group,
and put the Scenario command in that group.
- Right-click on the Ribbon, and click Customize the Ribbon
- From the drop down list at the top left, select All Commands
- In the list of commands that are currently on the Ribbon, click
the plus sign for Data, then click Data Tools. That group contains
the Scenario Manager, so we'll add the new group beside it.

- Below the list, click the New Group button.
- Then, click the Rename button, type Scenario as the name for the
group, and click OK

- At the left, in the list of All Commands, scroll down to find
Scenarios
- Make sure that the new Scenario group is still selected in the
list at the right.
- Click on Scenarios, then click Add, to put Scenarios in the Scenario
group.

- Click OK, to close the window, then click the Data tab, and select
a Scenario to view.

Get the Sample File
Get the zipped sample
Excel Scenario file
There is another Excel
Scenario example on the Contextures blog.
More Scenario Tutorials
Scenario Summaries
Automatically Show Scenarios
Scenarios -- Programming
Scenarios Excel 2003