WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 525 reviews for our classroom and online training
If you found this blog useful and you’d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

Using Scenarios in Microsoft Excel
Part one 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!

1. Using Scenarios in Microsoft Excel (this blog)
2. Viewing Scenarios in Microsoft Excel

Posted by Andrew Gould on 27 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Using Scenarios in Microsoft Excel

When you have a sophisticated model set up in Excel you'll often want to alter the input values to test what effect it has on the outputs.  Scenarios are designed to help you quickly switch between different sets of inputs for a model without having to manually type in different values time and time again.

You can watch a video version of this scenarios tutorial, or see a full range of resources for our Excel training range here.

## The Example Model

To demonstrate how scenarios work we'll use a simple mortgage calculator model, as shown below:

This simple model contains four input cells (the yellow-shaded ones) and four calculated cells.

You can download a copy of the file used in this tutorial by clicking here.

## A Note on Range Names

Scenarios work best when you have used range names to refer to the input cells.  If you're not sure how to use range names in Excel it might be worth reading this blog first.

The advantage of range names is that it makes models much easier to read. Here we've selected cell B2, but you can see at the top left of the screen that we've also created a range name - House_price

Range names aren't essential in order in to successfully use scenarios, but they do make the process much easier!

## Creating a Scenario

Creating a scenario essentially means saving a set of input values for a worksheet.  To do this:

1. Go to the worksheet containing your input values.
2. From the ribbon, select: Data -> What-If Analysis -> Scenario Managerï¿½

To begin with you won't have any scenarios in the worksheet.  The next step is to add some.

Complete this dialog box to create a scenario. The numbered steps are explained below.

1. Enter a name for the scenario.
2. Choose the cells whose values might change in different versions of the scenario.  Here we've picked the four cells that we highlighted in yellow.

Each scenario can have up to 32 changing cells.

1. You can optionally add a description - the one shown here is the default.
2. Click OK to move on to the next dialog box and enter the values for the scenario.

Enter the values for the different cells - here you can clearly see the advantage of using range names!

1. When you have finished entering values for the input cells you can either click Add to immediately create another scenario, or OK to go back to the Scenario Manager dialog box.

When you've finished adding scenarios you can see a list in the Scenario Manager dialog box.

Here we've created six scenarios, each with a different set of input values for our model.

## What's Next?

Now that you've created some styles, the next step is to view the results.  The next part of this series shows you how to do exactly that, as well as showing you how to add an extra option to your toolbar to make viewing scenarios even quicker.

1. Using Scenarios in Microsoft Excel (this blog)
2. Viewing Scenarios in Microsoft Excel