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.
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
To create a scenario, follow these steps:
Display the worksheet that contains your values and
choose Tools | Scenarios to display the Scenario Manager.
Type a name for the scenario.
Click the Collapse Dialog button next to the Changing
Cells field and select the cells that contain the values you want to
Type the new scenario values in the dialog boxs
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
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
To create a scenario summary, follow these steps:
Choose Tools | Scenarios.
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.