This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Using Scenarios in Microsoft Excel Part two of a two-part series of blogs
Scenarios are incredibly useful when performing "what-if analysis". They allow you to quickly swap a range of input values to test different possibilities without having to retype any values. Read on to find out how they work! Using Scenarios in Microsoft Excel Viewing Scenarios in Microsoft Excel (this blog)

Using Scenarios in Microsoft Excel

Part two of a two-part series of blogs

Scenarios are incredibly useful when performing "what-if analysis". They allow you to quickly swap a range of input values to test different possibilities without having to retype any values. Read on to find out how they work!

Using Scenarios in Microsoft Excel
Viewing Scenarios in Microsoft Excel (this blog)

Posted by Andrew Gould on 27 February 2012

In this blog

Viewing Scenarios Using the Scenario Manager
Adding the Scenario Tool to the Toolbar
Viewing a Scenario Summary

Viewing Scenarios in Microsoft Excel

Once you have created scenarios in a worksheet you'll want to be able to view them. This part of the series explains how to switch between different scenarios and how to modify a toolbar to create a quick way to do this.

Viewing Scenarios Using the Scenario Manager

The standard way to view a scenario is to use the Scenario Manager dialog box. To do this:

From the ribbon select: Data -> What-If Analysis -> Scenario Manager...

Use this dialog box to view different scenarios. The numbered steps are described below.

Select the scenario you wish to view.
Click the Show button to view the selected scenario.

When you click the Show button the input cells will change to the values saved in the selected scenario.

Adding the Scenario Tool to the Toolbar

The only downside to the above method of viewing scenarios is that you must have the Scenario Manager dialog box open in order to do it. A more convenient solution is to add the Scenario tool to one of the toolbars. To do this:

Click the drop arrow at the end of the Quick Access Toolbar, as shown below:

The image shown here is taken from Excel 2010, but you can do the same thing in later versions of Excel!

Click the More Commands... option to customise the toolbar.

Follow the numbered steps as described below.

Select All Commands from the drop down list.
Find and select the Scenario option.
Click the Add>> button to transfer the tool to the list on the right.
Make sure that the Scenario tool appears in the list on the right and then click OK.

Once you've added the Scenario tool to the toolbar you can use it to switch between different scenarios.

Click the Scenario tool to see a list of available scenarios and then click on the one you want to see.

Viewing a Scenario Summary

Rather than viewing one scenario at a time, you might want to see a side-by-side comparison of the scenarios in a sheet. To do this:

Select the worksheet containing your scenarios.
From the ribbon select: Data -> What-If Analysis -> Scenario Manager...

You should be looking at the now-familiar Scenario Manager dialog box.

On the dialog box, click the Summary... button.

Use this dialog box to set up the summary report. The numbered steps are described below.

Choose which type of report you would like to create. Here we're choosing the basic type of summary report.
Choose the cells containing calculations whose results will change based on the values altered by the scenarios. Here we know that the Loan and Monthly payment will change as we apply different scenarios so we select these as our result cells.
Click OK to view the summary report.

A scenario summary report. The advantage of using range names is again quite obvious!

The summary report will appear on a separate worksheet and show a side-by-side comparison of the scenarios. The values in the summary are not linked to any other values, so if you change anything on the original worksheet you'll need to run the summary report again.

You can download the finished version of the file used in this article by clicking here, or see our full range of Excel training courses.

Parts of this blog
Using Scenarios in Microsoft Excel Viewing Scenarios in Microsoft Excel (this blog)

Some other pages relevant to the above blogs include:

Excel learning

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Viewing Scenarios in Microsoft Excel

Viewing Scenarios Using the Scenario Manager

Adding the Scenario Tool to the Toolbar

Viewing a Scenario Summary

Head office

London

Manchester