It is every spreadsheet user’s worst nightmare – you’ve worked on a workbook for a period of time, and then accidentally close it without saving. Or the power goes out, or Excel crashes and the list of spreadsheet hazards goes on and on.
If you’re using Excel 2010 or later, there’s a pretty good chance you can mitigate much – but not all – of the risk related to unsaved workbooks. Regardless, in any version of Excel, you can raise the odds of having a recovered copy of your work available after a software crash.
Excel has long had an AutoRecover feature that’s designed to help you recover unsaved workbooks should Excel crash. If any version of Excel crashes, you sometimes get the opportunity to recover at least some of your work from the Document Recovery pane. However, this feature is limited to spreadsheets that were open in Excel at the time the program or your computer crashed.
Although somewhat helpful, the Document Recovery feature doesn’t protect workbooks you inadvertently closed without saving by clicking “no” on that eponymous “Do you want to save the changes you made?” prompt. However, in Excel 2010 and later, you have the ability to not only recover unsaved files, but sometimes recover a version of a file from a few minutes earlier. This is helpful when you make a blunder that you can’t undo, or when you want to see how a workbook looked a few minutes earlier. To do so, choose File, Info, and then look for the Manage Versions button. If previous versions are shown, you can open these alongside the most current version of your workbook and copy and paste data between the workbooks as needed.
In any version of Excel, you should carry out the following steps to increase the odds of being able to recover unsaved work:
Excel 2010 and later: As shown in Figure 3, choose File, Options, Save, and then change the Save AutoRecover Information setting to every two minutes, down from the default of every ten minutes.
Excel 2007: Click the Office button, choose Excel Options, Save, and then change the Save AutoRecover Information setting to every two minutes, down from the default of every ten minutes.
Excel 2003 and earlier: Choose Tools, Options, click the Save tab, and then change the Save AutoRecover Information setting to every two minutes, down from the default of every ten minutes.
Bear in mind that Excel won’t necessarily save your work every two minutes. Casual observation shows that Excel saves temporary versions on a somewhat random basis, but regardless, lowering the setting to every two minutes increases the potential frequency for new versions to be saved.
Although these features offer a modicum of safety against crashes, your best defense is to save frequently and to create multiple versions of your documents