Excel.Tips.Net ExcelTips (Menu Interface)

Creating Scenarios

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Creating Scenarios.

You already know that Excel provides the ability to play "what if" with your worksheets. As an adjunct to this capability, Excel provides the Scenario Manager, which allows you to save different data scenarios. Using this tool you can save different variables for your sheet and call them up quickly and easily.

As an example, let's suppose that you work for Talbot Industries, and you have been charged with developing a profitability analysis for a new product—the Potato Chip Peeler. You develop your sheet, taking into account all the appropriate information. However, you know that it is prudent at your company to actually prepare three forecasts. The first would be the worst-case scenario, the second is the most-likely scenario, and the final is the best-case scenario.

To save a scenario, follow these steps:

  1. Make sure the worksheet reflects one of your scenarios.
  2. Select the cells that will be changing in the scenario. For instance, you might select the cells at B4:B11 and F5:F11, if these are the cells that will change from one scenario to another.
  3. Choose Scenarios from the Tools menu. Excel displays the Scenario Manager dialog box. (See Figure 1.)
  4. Figure 1. The Scenario Manager dialog box.

  5. Click on the Add button. Excel displays the Add Scenario dialog box. The cells you selected in step 2 should already be indicated in the Changing Cells field. (See Figure 2.)
  6. Figure 2. The Add Scenario dialog box.

  7. Enter a name for the scenario, such as Most Likely or Worst Case.
  8. Click on the OK button. Excel shows you a dialog box containing a list of the values in the scenario. Each cell is listed along with its value. If the cell has a name assigned to it, the name is used. If not, the cell address is used.
  9. If you like, you can change the values stored in the scenario.
  10. When you are satisfied, click on the OK button. The scenario is then saved under the name you specified and your original worksheet is unchanged.

Once you have a scenario saved, you can make changes to your workbook and save your changed figures under a different scenario.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2860) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Creating Scenarios.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.