Disaster-Proofing Excel Spreadsheets

You’ve likely experienced that sinking feeling where hours of work suddenly vanish from your screen because you didn’t save your work. In this article I’ll describe ways you can create fallback positions for your work. I’ll show you how to optimize your AutoRecover settings, set key workbooks to back themselves up automatically, use Office 365 to create resilient connections to other workbooks, and use the Scenario Manager to back-up key inputs.

You’ve probably lost count of the times you’ve heard to press Ctrl-S periodically to save your work. Let’s first create a backstop, since life is full of best-laid plans. Go to the Save section of the Excel Options dialog box and change the Save AutoRecover Information to every 2 minutes (down from every 10 minutes). This shrinks the frequency with which Excel automatically saves a back-up copy of your work. This doesn’t mean that Excel will save your work every 2 minutes, but it will do so much more frequently than the default interval of 10 minutes. In Excel 2010 and later you can access these AutoRecover files in two ways:

As back-ups are created the Manage Workbook (or Versions in Excel 2010/2013) section of the Info tab on Excel’s File menu will display timestamps. Click on any one to open the AutoRecover copy and recover work from earlier in the current work session. These AutoRecover copies vanish when you close the document, with one exception.
The most recent AutoRecover version of the spreadsheet is archived temporarily if you close a document without saving it. To access files you closed recently without saving, click Recover Unsaved Workbooks at the bottom of the Open or Recent menus in Excel. Documents in the Unsaved Files folder only remain there for a couple of days and then are automatically deleted.

You can also instruct all versions of Excel to automatically back-up selected documents:

Click the Tools button within the Save As dialog box, and then choose General Options.
Click Always Create Backup, and then click OK.

You must enable this option one workbook at a time but going forward Excel will create a back-up copy every time you save the live copy of the file. The back-up will have the prefix “Backup Of” and will have a .XLK file extension. This back-up is replaced with the most recent previous version of your workbook each time you save.

Office 365 offers even more robust disaster-proofing for documents saved to Microsoft’s OneDrive service. By default, an AutoSave option is turned on, meaning that Excel automatically saves the file periodically. These archived versions can be accessed by clicking the menu that appears in Excel’s title bar. You can turn off AutoSave in Office 365 by way of a Quick Access Toolbar icon but must do so one workbook at a time.

Disaster-proofing spreadsheets can go far beyond simply creating fallback positions for your workbooks. For instance, Office 365 users can create a query to pull in data from another workbook, instead of relying on workbook linking formulas:

In Office 365, choose Data, Get Data, From File, and then From Workbook.
When prompted select the workbook you wish to link to, and then click Open.
Select the worksheet you wish to retrieve data from, and then click Load.

The resulting data will appear in a list form in your spreadsheet. To test the resilience of this data, select a block of cells within the list, type a zero, and then press Ctrl-Enter. In most workbooks this action could be catastrophic because it entails overwriting data in the spreadsheet. Instead, in this case you can right-click any cell within the list, and then choose Refresh. The data from the external workbook will reappear, and the crisis is averted. If you wish to have this list refresh automatically, click the Refresh command on the Design menu (which appears when you click any cell within the list), and then choose Connection Properties. Within the Query Properties dialog box click Refresh Data When Opening the File.

One final approach that I’ll mention is available in all versions of Excel. You can use the Scenario Manager to back up a set of inputs used in your spreadsheet. Each scenario is limited to up to 32 cells, but you can create as many scenarios as you wish, spanning as many sets of 32 cells as you want:

Select up to 32 cells within a worksheet. Scenarios are tied to specific worksheets, as opposed to a given workbook.
Choose Data, What-If Analysis, and then Scenario Manager.
Click Add to display the Add Scenario dialog box.
Provide a scenario name. The Changing Cells will be prefilled based on your action in step 1, so click OK.
A Scenario Values dialog box will display the inputs being captured. Click OK if you only want to create single scenario or click Add to create an additional scenario.

Going forward whenever you choose Scenario Manager from the What-If Analysis menu on the Data menu you’ll be able to select a scenario name and click Show. You can apply as many scenarios as you wish in succession or swap different sets of inputs into a block of cells. This is another way of having a fallback position in case key inputs get changed within a spreadsheet.

Accounting