How To Create A Scenario Pivot Table In Excel

Contextures

Excel Scenario Summaries

After you create 2 or more Scenarios in Excel, use a Scenario Summary to show an overview of the data

Introduction

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.

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

Important Note: 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 reports to compare the data, or print the current status, and then delete the sheets, to prevent confusion.

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

scenario budget

Create a Scenario Summary

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

Although Excel scenarios can be complex, a simple example is used here. The annual budget forecasts from both the Finance and Marketing departments have been entered, and stored as scenarios.

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.

To create a Scenario Summary:

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 Summary
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 Summary sheet is added to the workbook

View the Scenario Summary

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

Select the Scenario Summary worksheet
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, cell B1 is named Dept, and you can see that name in cell C6 on the Scenario Summary sheet.

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

scenario summary

Create a 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 the Scenario PivotTable Report

Select the Scenario PivotTable worksheet
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.

scenario pivottable

Get the Scenario Sample File

Get the zipped sample Excel Scenarios file