Excel Scenarios Create and Show

Use Excel Scenarios to store several versions of the data in a worksheet. For example, create 2 different budgets - best case and worst case. Download the sample file and watch the video or follow the written instructions

Author: Debra Dalgleish

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 First Excel Scenario

1. On the Ribbon's Data tab, click What If Analysis
2. Click Scenario Manager

   

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

    

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

    

Create Second Excel Scenario

1. To prepare for the Finance scenario, change the values in cells B1, B3 and B4, as shown below



2. On the Ribbon's Data tab, click What If Analysis, then click Scenario Manager.
3. In the Scenario Manager, click the Add button
4. Type name for the second Scenario. For this example, use Finance.
5. The Changing cells box should show the previous selection -- B1,B3:B4 -- so leave that as is.
6. Press the Tab key, to move to the Comment box
7. (optional) Enter a comment that describes the scenario.
8. Click the OK button
9. The Scenario Values dialog box opens, with a box for each changing cell.
10. Click the OK button, to return to the Scenario Manager.
11. 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:

1. On the Ribbon's Data tab, click What If Analysis, then click Scenario Manager.
2. The Scenario Manager dialog box opens, showing a list of scenarios in the workbook
3. In the list of Scenarios, select Marketing
4. Click the Show button
5. 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.

1. Right-click on the Ribbon, and click Customize the Ribbon
2. From the drop down list at the top left, select All Commands
3. 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.

   

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



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

   

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

   

Spending Scenarios - Low, Mid, High

For holiday spending this year, what is your best case scenario? Do you have a big budget to spend for the holidays? Or, in your worst case scenario, will you spend as little as possible? Or will your holiday budget be somewhere in the middle?

You can use Excel Scenarios to store several versions of a budget, and compare the results.

In the steps below, see how to set up a worksheet where you can compare three scenarios for holiday spending.

Set Up the Worksheet

The first step is to set up the worksheet. Some of the cells will be the same in each Excel scenario, and other cells will change.

Note: There’s a limit of 32 changing cells in an Excel Scenario.

• On a worksheet named Budget, add headings, spending categories, and amounts, as shown in the screen shot below.
• Add a Total label, and a sum of the spending amounts.

Create the First Scenario

The first scenario is for Extravagant spending, which will contain the highest amounts.

• On the Ribbon, click the Data tab.
• Click What-If Analysis, then click Scenario Manager.

• In the Scenario Manager, click Add
• Type name for the Scenario. For this example, use High.
• Clear the Changing cells box
• With the cursor in the Changing cells box, click cell B4 on the worksheet. That’s one of the cells that will change in each scenario.
• Hold the Ctrl key, and select cells C6:C11. Do not include any of the category labels, or the total cells.
• (optional) Enter a comment that describes the scenario.
• Click OK to close the Edit Scenario box.

Add the Scenario Values

The Scenario Values dialog box opens, with a box for each changing cell. It automatically displays the current value in each changing cell. You could modify one or more of the existing values, or leave them as is.

We’ll make the Gifts – Family amount a bit higher, and leave the other values untouched.

• For item 5, change the value from 500 to 600.
• Click OK to return to the Scenario Manager. Notice that the value on the worksheet didn’t change – it still shows 500 as the amount for Gifts – Family.

Create Another Scenario

You can add more scenarios by changing the worksheet values, and following the steps that you used to build the first scenario.

Or, you can add an Excel scenario directly into the Scenario Manager.

1. In the Scenario Manager, click Add
2. Type name for the next scenario. For the second scenario, use Mid.
3. Leave the existing cells in Changing cells box
4. (optional) Enter a comment that describes the second scenario.
5. Click OK to close the Add Scenario box.
6. In the Scenario Values dialog box, enter the worksheet heading and values for the second scenario.
7. Click OK to return to the Scenario Manager.
8. Create the third scenario – Low – and enter the lowest amounts for that scenario.
9. Click Close, to return to the worksheet.

Show a Scenario

Once you have created the Excel Scenarios, you can show them. On the worksheet, the original values for Extravagant scenario are showing. To change to a different scenario:

1. On the Ribbon, click the Data tab.
2. Click What-If Analysis, then click Scenario Manager. (In earlier versions, click Tools>Scenarios)
3. In the list of Scenarios, click on a Scenario name
4. Click Show, then click Close.

Show Excel Scenario Summary

After you create the Excel Scenarios, you can view them in an Excel Scenario Summary report. These scenario reports let you see the values and totals side-by-side, for an overall comparison.

Note: The Excel Scenario Summary does NOT update automatically if you change the scenario values. You can delete the old summary and create a new one.

To create a Scenario Summary:

• On the Ribbon, click the Data tab.
• Click What-If Analysis, then click Scenario Manager. (In earlier versions, click Tools>Scenarios)
• In the Scenario Manager, click the Summary button
• In the Scenario Summary dialog box, for Report type, select Scenario Summary
  • To see a Scenario PivotTable report, and its limitations, go to the Scenario Summaries page.
• Click in the Result cells box, and on the worksheet, click the Total calculation cell (C12).
• Click OK, to close the dialog box.

A Scenario Summary sheet is added to the workbook.

• To show or hide the details, click the + / – buttons at the left side and top of the worksheet

Improve the Scenario Summary

In the Scenario Summary shown above, the changing cells are shown as addresses. If you name the value cells, the Scenario Summary will show those names, instead of the cell addresses

You could change the colour scheme too, unless you love grey and purple!

Get the Sample File

Get the zipped sample Excel Scenario file, with the Marketing and Finance example. The zipped file is in xlsx format, and does not contain any macros

More Scenario Tutorials

Scenario Summaries

Automatically Show Scenarios

Scenarios -- Programming

Data Table Examples

Scenarios Excel 2003