Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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!
|
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.
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.
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 essentially means saving a set of input values for a worksheet. To do this:
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.
Each scenario can have up to 32 changing cells.
Enter the values for the different cells - here you can clearly see the advantage of using range names!
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.
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.