Curt Frye That Excel Guy Scenarios Article

Title Graphic for That Excel Guy Index Page

Curt Frye That Excel Guy Link to Home Page Curt Frye That Excel Guy Writing Page Curt Frye That Excel Guy Speaking Page Link Curt Frye That Excel Consulting Page Link Curt Frye That Excel Guy Resources Link Curt Frye That Excel Guy Resume Link Curt Frye That Excel Guy Contact Link

 

Backing Up Worksheet Data in Worksheets with Scenarios 

Businesses, and other organizations, often project revenues and expenses using the best case, middle case, and worst case approach. You can get a lot more complex with your projects and create cases for each element in your project or enterprise, but the number of variable can get out of hand quickly. Thats why most organizations consider quite a few variables but leave the majority of them out of the final report. 

Creating Scenarios 

In Excel, you can create scenarios to store different sets of values that represent your best, worst, and middle cases. You should put the values from the middle case in the worksheet and create scenarios to represent all three cases. It might sound odd that youd want to create a case to represent your initial values, but it makes it easier to switch between cases if you dont have to worry about using the Undo button to get back to your base worksheet values. 

Suppose you have the data table displayed in the following graphic: 

To create a scenario, follow these steps: 

  1. Display the worksheet that contains your values and choose Tools | Scenarios to display the Scenario Manager.

  2. Click Add.

  3. Type a name for the scenario.

  4. Click the Collapse Dialog button next to the Changing Cells field and select the cells that contain the values you want to change.

  5. Type the new scenario values in the dialog boxs fields.

  6. Click OK to create the scenario, or click Add to save your work and create a new scenario that changes the same cells. 

After you create a scenario, you can display it by choosing Tools Scenarios, clicking the scenario to display, and clicking Show. To remove a scenarios changes, click the Undo toolbar button. 

Note: You can change up to 32 cells in a scenario. 

Restoring Your Original Worksheet Values 

Scenarios are extremely useful, but they do have a dark side. If you close your workbook while a scenario is in effect and have Excel save your changes, Excel overwrites the original values with the scenario values displayed when you last saved your workbook. Yes, your values are gone! But theres a way to ensure youve recorded your original data: you create a scenario summary. 

Tip: Yes, you can avoid losing your original data by making a backup copy of your workbook, but this article assumes you either didnt make a backup copy or you cant access it from the computer youre using. 

To create a scenario summary, follow these steps: 

  1. Choose Tools | Scenarios.

  2. Click Summary.

  3. Click OK. 

When you make a scenario summary, Excel creates a new worksheet named Scenario Summary, lists every cell that is changed in at least one scenario, displays the original cell values, lists the scenarios, and records each cells changed value (if any) under each scenario. The table appears in the following graphic. 

In the worst case, you can copy the data from the Scenario Summary Worksheet to the original cells and restore your data. By creating the scenario summary, you record your original worksheet data and avoid the disaster of lost data that can occur if you save your workbook while a scenario is in effect.