How to Create a Scenario Pivot Table Report in Excel

Ever wished you could see how different business scenarios - like a best-case, worst-case, and most-likely sales forecast - impact your final numbers all in one report? Instead of building dozens of separate spreadsheets, you can use Excel's powerful What-If Analysis tools. This tutorial will walk you through, step-by-step, how to use the Scenario Manager combined with a PivotTable to build a dynamic report for comparing outcomes.

What is a Scenario Pivot Table, Anyway?

First, let's clear up a common point of confusion. There isn't a feature in Excel literally called a "Scenario Pivot Table." Instead, it is a special type of report you generate from the Scenario Manager. Think of them as two tools working together:

• Scenario Manager: This is where you store different sets of input values (your variables). For example, one scenario might have your marketing spend at $5,000, while another has it at $10,000.
• PivotTable Report: This is the output. Excel takes all the scenarios you've created, runs the calculations for each, and neatly organizes the results into a PivotTable for easy side-by-side comparison.

The goal is to move from manually changing numbers and seeing what happens to an automated report that displays every possible outcome at once. It’s an incredibly efficient way to review forecasts, budgets, and project plans.

Before You Begin: Set Up a Solid Data Model

Your scenario analysis is only as good as the model you build. Before you touch the Scenario Manager, you need a worksheet where your inputs (the things you'll change) are clearly separated from your outputs (the results that are calculated automatically). A solid model is the foundation for everything to come.

Let's use a simple profit projection for an online store as our example.

Step 1: Define Your Inputs and Outputs

Create a simple table with your key assumptions. These are the variables you plan to change for each scenario. Below them, add your calculation formulas. It’s crucial that the outputs are linked to the inputs via formulas. This way, when the Scenario Manager swaps out the input values, the results update automatically.

Here's what our example looks like:

| --- A -----------------|-- B ----- | | Inputs: | Values | | Units Sold | 5,000 | | Price per Unit | $25.00 | | Cost per Unit | $10.00 |

Step 2: Name Your Cells (A Pro Tip for Clean Reports)

Before creating scenarios, take a minute to name your input and output cells. This sounds minor, but it makes your final report infinitely more readable. If you skip this, your PivotTable will be filled with confusing cell references like "$B$2" instead of clear labels like "Units_Sold."

Here’s how to do it:

1. Click on cell B2.
2. Go to the Name Box, which is located to the left of the formula bar.
3. Type in a descriptive name like Units_Sold and press Enter. (Note: Names cannot have spaces).
4. Repeat this for all your input and output cells (B3 becomes Price_Per_Unit, B9 becomes Total_Cost, etc.).

With a clean, formula-driven model and named cells, you're ready to start building scenarios.

Step-by-Step Guide to Creating Scenarios

Now we get to the core of the task: defining the different scenarios you want to compare. This is all done within the Scenario Manager.

Step 1: Open the Scenario Manager

Navigate to the Data tab on the Ribbon. In the Forecast group, click on What-If Analysis, then select Scenario Manager from the dropdown menu.

Step 2: Add Your First Scenario (Baseline)

Your first scenario should represent your current or most likely expectations. We'll call this our "Baseline."

1. In the Scenario Manager dialog box, click Add....
2. A new window pops up. Fill it in:
3. Click OK.
4. The Scenario Values window appears next. Since this is our baseline, the values already in your worksheet are correct. Just click OK to save it.

Your new "Baseline" scenario now appears in the Scenario Manager.

Step 3: Add Additional Scenarios (Best Case & Worst Case)

Now, let's create two more scenarios to compare against the baseline.

1. With the Scenario Manager still open, click Add... again.
2. For the "Best Case":
3. Click Add... one last time to create the "Worst Case":

You should now see all three scenarios listed in your Scenario Manager: Baseline, Best Case, and Worst Case. You can click on any scenario and then click the "Show" button to see your original worksheet update instantly. This confirms your model is working correctly!

Generating Your Scenario PivotTable Report

With your scenarios saved, generating the comparison report takes just a few clicks.

1. In the Scenario Manager dialog box, click the Summary... button.
2. A small Scenario Summary window will appear. This is the crucial step.
3. Click OK.

Instantly, Excel will create a brand new worksheet named "Scenario PivotTable" containing a ready-made PivotTable. It displays the values from your changing cells and result cells for each of the three scenarios you created.

Customizing and Analyzing Your Report

The default PivotTable is functional, but it might not be perfect. You can now use all the regular PivotTable tools to format it for clarity and presentation.

Cleaning Up the Report

The "PivotTable Fields" pane on the right allows you to structure the report. Because you named your cells earlier, the fields should be clearly labeled (e.g., "Profit_Loss" instead of "$B$10").

• Filter Unnecessary Information: The default report shows your "Changing Cells" (the inputs) and "Result Cells" (the outputs). If you only want to focus on the results, you can drag the "Changing Cells" field out of the Rows area in the PivotTable Fields pane.
• Organize Rows and Columns: "Result Cells" should be in the Rows area and "(Scenario)" should be in the Columns area for a classic comparison view. If they're swapped, just drag and drop them to the correct areas.
• Format Numbers: The values in your PivotTable are just numbers. Right-click on them, choose Value Field Settings..., click Number Format, and select a format like Currency to make the report easier to read.

Visualizing with a PivotChart

A great-looking chart can make your findings much easier to understand. You can easily create a PivotChart that is linked directly to your scenario PivotTable.

1. Click anywhere inside your cleaned-up PivotTable.
2. Go to the PivotTable Analyze tab on the Ribbon.
3. Click PivotChart.
4. Choose a chart style that works well for comparison, like a Clustered Column chart, and click OK.

You now have a chart that visually compares your profit, revenue, and costs across the three different business scenarios.

Making it Interactive with Slicers

Slicers are basically fancy filter buttons that make your report interactive and easy for others to use, even if they aren't Excel experts.

1. Select your PivotTable again.
2. On the PivotTable Analyze tab, click Insert Slicer.
3. Check the box next to "Result Cells" (or any other field you want to filter with) and click OK.

A slicer panel will appear. Now, you or your teammates can simply click on "Profit_Loss" or "Total_Revenue" in the slicer to instantly filter both the PivotTable and the connected PivotChart to show only that data. It's a fantastic way to present your findings in a meeting, allowing you to focus on different results on the fly.

Final Thoughts

You've just learned how to use Excel's Scenario Manager alongside a PivotTable to build an interactive forecasting report. This method moves you beyond simple what-if guesswork into a structured analysis, providing a clear comparison of how different variables can impact your bottom line and helping you prepare for multiple business futures, not just one.

While powerful, building scenario reports in Excel often involves significant manual setup, from carefully constructing the initial data model to remembering to update scenarios as your assumptions change. We built Graphed to remove this kind of friction. Instead of manually inputting variables, you can connect your live sales and marketing data directly, then use natural language to analyze them. You can ask questions like "Compare our profit forecast if marketing spend increases by 20% vs. if it decreases by 10%" and get an interactive dashboard in seconds, not hours. It streamlines analysis by making your data conversational and ensuring your reports are always pulling the latest numbers, automatically.