Search Contextures Sites

Excel Scenarios -- Create and Show

Set up the Excel Scenarios Worksheet   Create the first Excel Scenario   Create the second Excel Scenario  Show an Excel Scenario   Download the zipped sample Excel Scenario file For Excel 2003 instructions see: Excel 2003 Scenarios -- Create and Show

You can use Excel Scenarios to store several versions of the data in a worksheet. For example, when preparing a budget, the Marketing and Finance departments may have different forecasts for sales. You can store each forecast as a Scenario, print them separately, or compare them side-by-side.

Set up the Excel Scenarios Worksheet Although Excel scenarios can be complex, a simple example is used here. Delete all sheets except Sheet1 Rename Sheet1 as Budget On the Budget sheet, enter the Marketing budget, as shown at right Name the following cells (there are Naming instructions here: Name a Range). Naming the cells is not required, but will make it easier to manage the scenarios, and read the reports: Name cell B1 as Dept Name cell B3 as Sales Name cell B4 as Expenses Name cell B6 as Profit In cell B6, enter the following formula:       =Sales - Expenses

Create the First Excel Scenario On the Ribbon's Data tab, click What If Analysis Click Scenario Manager

In the Scenario Manager, click the Add button Type name for the Scenario. For this example, use Marketing. Press the Tab key, to move to the Changing cells box On the worksheet, select cells B1 Hold the Ctrl key, and select cells B3:B4 Note: There is a limit of 32 changing cells Press the Tab key, to move to the Comment box (optional) Enter a comment that describes the scenario. Click the OK button

The Scenario Values dialog box opens, with a box for each changing cell. You could modify these values, but in this example they contain the values currently on the worksheet, and don't need to be changed. Click the OK button, to return to the Scenario Manager. Click the Close button, to return to the worksheet

Create the Second Excel Scenario To prepare for the Finance scenario, change the values in cells B1, B3 and B4, as shown at right On the Ribbon's Data tab, click What If Analysis, then click Scenario Manager. In the Scenario Manager, click the Add button Type name for the second Scenario. For this example, use Finance. The Changing cells box should show the previous selection -- B1,B3:B4 -- so leave that as is. Press the Tab key, to move to the Comment box (optional) Enter a comment that describes the scenario. Click the OK button The Scenario Values dialog box opens, with a box for each changing cell. Click the OK button, to return to the Scenario Manager. Click the Close button, to return to the worksheet

Show an Excel Scenario Once you have created Scenarios, you can show them. In this example, the Finance scenario is currently visible. To change to a different scenario: On the Ribbon's Data tab, click What If Analysis, then click Scenario Manager. In the list of Scenarios, select Marketing Click the Show button Click the Close button. There is another Excel Scenario example on the Contextures blog.   Download the zipped sample Excel Scenario file

1. Excel Scenarios -- Create and Show   2. Excel Scenarios -- Scenario Summaries   3. Excel Scenarios -- Automatically Show Scenarios     4. Excel Scenarios -- Programming

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2012 All rights reserved.	Debra Dalgleish