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 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!
|
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.
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.
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.
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 |
---|
|
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.