Data Analysis [Part 3 of 5] – Scenario Manager in Excel

This is the third article of the five-part series on Data Analysis in Excel. In this section, I will show you how to use the Scenario Manager in Excel.

Download File

Watch Video – Scenario Manager in Excel

Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change.

Suppose you have a dataset as shown below and you want to calculate the profit value:

Scenario Manager in Excel - Data set

The Profit value is dependent on 3 variables – Sale Quantity, Price per Unit, and the Variable Cost per Unit. Here is the formula I have used to calculate the profit:


The idea is to see how this final result changes when we change these dependent variables.

As shown in the first 2 articles of this series, if you only have one or two variables changing, you can create one variable or two-variable data table. But if you have 3 or variables that can change then scenario manager is the way to go.

Setting up Scenario Manager in Excel

  • Go to Data Tab –> Data Tools –> What-If Analysis –> Scenario Manager.Accessing Scenario Manager in Excel
  • In the Scenario Manager dialogue box, click on Add.Scenario Manager in Excel - Add
  • In the Add Scenario dialogue box, fill in the following details:
    • Scenario name: Worst Case
    • Changing cells: $B$2,$B$3,$B$5 (you can also select it by pressing the CONTROL button and using mouse left-click).
    • Comment: Any comment you wish you add. You can also leave this blank.Scenario Manager in Excel - Add a Scenario Details
  • Click OK. This opens the Scenario Values dialogue box.
  • In the Scenario Values dialogue box, fill in the following values (since this is the worst case scenario, enter the values accordingly). If you create names for each cell, that name is visible instead of the cell address:
    • $B$2: 50
    • $B$3: 30
    • $B$4: 30Scenario Manager in Excel - Scenario Value
  • Click OK (Click on Add if you want to add another scenario).

This creates the Worst Case scenario for this data set. You can similarly follow these steps and create multiple scenarios (for example, Worst Case, Realistic, Best Case).Scenario Manager in Excel - Added Scenarios

Once you have created all the scenarios, you can view the result from each of the scenarios by simply double-clicking on any of the scenarios. As you double click, the values would change based on that scenario.

Additionally, you can also create a summary of all the scenarios.

Create a Summary of all the Scenarios

  • Click on the Summary button in the Scenario Manager dialogue box.Scenario Manager in Excel - Summary Button
  • In the Scenario Summary dialogue box, select Scenario Summary or Pivot Table (these are the 2 ways to show summary). Also specify the Result cells (the cell where you have the output of this calculation; B6 in this example)Scenario Manager in Excel - Scenario Summary OPtions
  • Click OK. Instantly a new tab is created with the summary of all the three scenarios.Scenario Manager in Excel - Summary

Scenario manager in Excel is a great tool when you need to do sensitivity analysis. Simply create scenarios and a summary can be generated instantly, giving you a complete comparative overview.

Download File… Try it yourselfDownload File Pic

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe


Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

10 thoughts on “Data Analysis [Part 3 of 5] – Scenario Manager in Excel”

  1. Hi, I have a simple but important question.

    I’m trying to link and automate the scenario values. Instead of feeding it generic values, I’m using a cell reference, it works (instead of “50”, I’m using “=A1”). However, the values from that cell reference are being fixed and will not update when I change the scenario values in those cells.

    The problem is once I give it the cell reference as input, e.g. = A1, it takes the generic value inside A1 and fixes it as the scenario figure. When I manually modify this value in A1, the scenario doesn’t update because it’s no longer referring to the cell A1.

    Any ideas?



Leave a Comment



Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster



Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal


Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster