Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Excel Scenario Manager – Excelchat

Excel allows us to store different sets of data on the same cells on a worksheet through the Scenario Manager.  Each set of data is considered one scenario.

Figure 1.  Final result:  Scenario Manager

How to create scenarios?

We enter a scenario one by one.  Suppose we have three possible plans for a weekly production run.  In order to create a scenario for each plan, we follow these steps:  

  • Enter the plan in D2 with the corresponding number of cases in D5:D9.  
    • In D2, enter “Plan 1”
    • In D5:D9, enter the values 1300, 2000, 700, 1300, 1000
    • D10 is the sum of the cases with the formula =SUM(D5:D9)

Figure 2.  Entering the values for Scenario 1

  • Click Data tab > What-If-Analysis > Scenario Manager  

Figure 3.  Scenario Manager in Data tab

The Scenario Manager dialog box will appear.

Figure 4.  Scenario Manager dialog box

  • Click Add and enter the following in the textboxes in Edit Scenario:
    • Scenario name: Plan 1
    • Changing cells: D2,D5:D9
    • Comment: Plan 1

Note:

In order to select multiple cells in Changing Cells, click the button then press the Ctrl key while selecting the cells D2, D5:D9.  

Figure 5.  Edit Scenario dialog box

  • Click OK.  The Scenario Values dialog box will appear, showing the values for the first scenario.  

Figure 6.  Scenario Values dialog box

  • Click OK.  The Scenario Manager will show that we have successfully created a scenario named Plan 1.  

Figure 7.  Output: How to create a scenario

  • For the second and third scenarios, we use the following values and follow the same procedure above to create the three scenarios.  

Figure 8.  Sample data for the three scenarios

Scenario Manager

Now that we have created three scenarios, we can select any of the scenarios through the Scenario Manager by following these steps:

  • Click Data tab > What-If-Analysis > Scenario Manager  
  • Choose from the list of available scenarios and click Show

Figure 9.  Select a scenario

Scenario Manager will instantly show the values for the selected plan in cells D2, D5:D9.  

Figure 10.  Output: Scenario Manager

We can now browse from one scenario to another through the Scenario Manager.

Scenario Summary

We can also create a scenario summary or scenario analysis through these steps:

  • Click Data tab > What-If-Analysis > Scenario Manager  > Summary

Figure 11.  Summary option in Scenario Manager

  • In the Scenario Summary dialog box, tick Scenario summary and in Result cells, select cell D10, which contains the value for the total number of cases.  

Figure 12.  Scenario Summary

  • The scenario summary will be created in another sheet named “Scenario Summary

Figure 13.  Output: Scenario Summary

The scenario summary shows the values for the current selected scenario, Plan 2.  It also shows the values for all the scenarios, where the changing cells are filled with gray fill color.  

The result cells are displayed at the last row, showing the total number of cases for each of the scenarios.  

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc