Home > Skills > Data Entry > Scenarios

Excel Scenario Summaries

After you create 2 or more different Scenarios in Excel, use a Scenario Summary to show an overview of the data. This is a static report that is designed to show the Scenario data at a moment in time.

Introduction - Scenario Summary

A Scenario is one of Excel's built-in What-If Analysis tools, along with Goal Seek and Data Table.

Use Excel Scenarios to store several versions of the data in a worksheet. For example, when preparing an annual budget, the Marketing and Finance departments may have different forecasts for sales.

• There are instructions for creating Excel Scenarios here: Excel Scenarios -- Create and Show

You can store each department's forecast as a Scenario. Then, print the scenarios separately, or compare them side-by-side, with Scenario Summary reports.

Scenario Summary Warning

Before you build a Scenario Summary, it's important to keep this limitation in mind:

• A Scenario Summary is a static report that does not change if the Scenario data changes

To avoid confusion later, with outdated information in a Summary Report, I recommend that you take one of the following precautions:

1. After you print the Scenario report, delete it from the workbook. Later, you can create a new Summary, when you need the latest scenario data in a comparison.
2. OR, after printing the Summary, save the workbook, and close it. Then, make a copy of that file, with the report date in the file name, e.g. AnnualBudgets_20231218.xlsx. Then, go back to the original workbook, and delete the Scenario Summary sheet - you can see it later, in the dated workbook, if needed
3. OR, save each Scenario Summary on a sheet where the tab has the report date, such as "2023_12_18", and the Report date is clearly shown at the top of the report worksheet.

Before Creating a Scenario Summary

Before you create a Scenario Summary, name the Scenario input cells on the Excel worksheet.

This will make it easeir to read the data that's in the Scenario Summary. The report will show the variable cell names, instead of the input cell worksheet addresses.

In the budget example screen shot shown above, the following names were created (there are Naming instructions here: Name a Range).

• cell B1 - Dept
• cell B3 - Sales
• cell B4 - Expenses
• cell B6 - Profit

Video: Create a Scenario Summary

To see the steps for creating an Excel Scenario Summary, and adding Scenario to the Excel Ribbon, watch this short video. Written instructions are below the video

Create Scenario Summary

Although Excel scenarios can be complex, I've used a simple example here. The annual budget forecasts from two departments, Finance and Marketing, have been entered in an Excel workbook, and stored as Scenarios.

To see the two budget scenarios side-by-side, and compare them, you can use a built-in tool - the Excel Scenario Summary.

Open Scenario Manager

To ollow these steps to create a Scenario Summary report:

• On the Ribbon's Data tab, in the Data Tools group, click What-If Analysis
• Click the drop down arrow, and click Scenario Manager

The Scenario Manager dialog box opens, where you can see a list with each Scenario name.

Build the Scenario Summary

To create the Scenario Summary, follow these steps:

• At the right side of the Scenario Manager, click the Summary button
• In the Scenario Summary dialog box, for Report type, select Scenario Summary
• Press the Tab key, to move to the Result cells box
• On the worksheet, click on cell B6.
  • That is the Profit cell, and it changes, based on the sales and expense amounts entered for each Scenario.

• Click the OK button.
• A Scenario Summary sheet is added to the workbook  

View Scenario Summary

To compare the scenarios side by side, you can view the Scenario Summary sheet that was created.

1. Select the Scenario Summary worksheet 
2. To show or hide the details, click the + / - buttons at the left side and top of the worksheet 

In this example, the scenario cells on the Budget sheet have been named, and those names appear on the Scenario Summary sheet, making it easier to understand.

• For example, on the Scenario input sheet, cell B1 is named Dept
• You can see the Dept name in cell C6 on the Scenario Summary sheet.

There is no option for formatting the Scenario Summary when you create it. However, you can change the formatting in the completed report.

Important Notes

• The Scenario Summary and Scenario PivotTable Report are not linked to the Scenario data, and they do not update if the Scenario data changes.
• Use these Summary reports to compare the scenario data, or to print the current status
• After reviewing or printing the data, it's best to delete the Scenario Summary sheet, to prevent problems later, if someone mistakes this report for current data.

Create Scenario PivotTable Report

For a different view of the Scenario data, you can create a pivot table report:

Warning: This is a static report that does not change if the Scenario data changes. Delete this report after printing it, and create a new Summary when necessary.

• On the Ribbon's Data tab, click What-If Analysis
• Click the drop down arrow, and click Scenario Manager

• Click the Summary button
• In the Scenario Summary dialog box, for Report type, select Scenario PivotTable report
• Press the Tab key, to move to the Result cells box
• On the worksheet, click on cell B6.This is the Profit cell, and it changes, based on the sales and expense amounts.

• Click the OK button.
• A Scenario PivotTable sheet is added to the workbook 

View Scenario PivotTable Report

1. Select the Scenario PivotTable worksheet 
2. To rearrange the data, drag the field buttons to a different area of the Pivot Table. For example, drag the Dept,$B$3:$B$4 button from cell A4 (row area) to cell B3 (column area) 

There is no option for formatting the Scenario PivotTable Report when you create it. You can change the formatting in the completed pivot table, by selecting a different PivotTable Style.

Get the Scenario Sample File

Get the zipped sample Excel Scenarios file. The zipped file is in xlsx format, and does not contain any macros

More Tutorials

Create and Show Scenarios

Automatically Show Scenarios

Scenarios Programming