Excel tip: How to recover unsaved files

We’ve all done it at some time: accidentially closing an Excel workbook without remembering to save it. Or your computer crashes or the office is hit by a power cut. There’s no need to despair, writes David Ringstrom. Excel 2010/13 can help you recover any lost files.

If you're using Excel 2010 or later, there's a pretty good chance you can minimise work lost in unsaved workbooks. And whatever version of Excel you use, there are ways you can raise the odds of recovering your work after a software crash.

Excel’s AutoRecover routine is designed to help you recover unsaved workbooks, and should let you revert to the last saved version displayed in the Document Recovery pane. 

Unfortunately Document Recovery will only recover files that were open when Excel crashed and doesn’t protect workbooks you inadvertently closed without clicking yes to the “Do you want to save the changes you made?" prompt.

Excel 2010 and later versions give the ability to not only recover unsaved files, but can also sometimes recover a file from a few minutes earlier. This can help when you make a blunder you can't undo, or when you want to revert to a previous version of a workbook.

To go back to previous versions, choose File, Info, and then look for the Manage Versions button, as shown below. If previous versions are listed, you can open these alongside the most current version of your workbook and copy and paste data between them.

Whatever version of Excel you use, the following steps can improve the odds of recovering unsaved work:

• Excel 2010 and later: Choose File, Options, Save, and then change the Save AutoRecover Information setting to every two minutes, down from the default of every 10 minutes, as shown below. 
• 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 10 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 10 minutes. 

Bear in mind that Excel won't necessarily save your work every two minutes. From casual observation it appears that Excel saves temporary versions on a somewhat random basis, but lowering the setting to every two minutes increases the potential frequency for new versions to be saved.

In any version of Excel, documents presented in the Document Recovery pane are typically deleted when you close the pane and answer “yes” to the prompt that you no longer need them. In Excel 2010 and later, unsaved versions appear to linger for three or four days and then are swept away.

In a really bad situation, for example if the Document Recovery pane doesn't appear, or you’re working in Excel 2007 and earlier and want to try to access a version from a few minutes earlier, try using Windows Explorer to navigate to the folder listed in the AutoRecover File location. You might just get lucky and find an accessible version of your document there. 

Although these features offer some security against crashes, your best defence is to save frequently and to create multiple versions of your documents. Personally, I incorporate version numbers, such as 1.01, 1.02 into important spreadsheet file names so I can go back in time when necessary.