Recovering Unsaved Excel Workbooks

Columnist
Share this content
Tags
170

By David Ringstrom, CPA

It's 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 . . . 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.
 
As shown in Figure 1, 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 in Figure 1. However, this feature is limited to spreadsheets that were open in Excel at the time the program or your computer crashed.
 
 
Figure 1: The Document Recovery pane appears automatically when warranted in all versions of Excel.
 
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, as shown in Figure 2. 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.
 
 
Figure 2: Excel 2010 and later offers the ability to access file versions that were inaccessible in Excel 2007 and earlier.
 
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, as illustrated with the difference in times shown in Figure 2. Although I haven't studied it at length, my casual observation is 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.
 
 
Figure 3: In any version of Excel you should change the AutoRecover frequency to every two minutes.
 
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 access to those files. In Excel 2010 and later, my casual observation is that unsaved versions appear to linger for three or four days and then are swept away.
 
In a Hail Mary situation, such as 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, it's worth a shot to use 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. 
 
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. Personally, I incorporate version numbers, such as 1.01, 1.02, and so on into some of my spreadsheet file names so I can go back in time when necessary.
 
Read more articles by David Ringstrom.
 
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Replies

Please login or register to join the discussion.

Thank you. I had saved the wrong version of a recovered word document and lost a lot of good work. Your solution is so appreciated because I got the document back.

I'm so glad to hear that! Thank you for taking a moment to share your experience.

HI David, I had my Excel workbook open that had been previously saved. I worked on the spreadsheet for an additional hour saving as I went along. The only caveat may be that I was updating this Spreadsheet during a Conference call and may have either pulled the Spreadsheet from the meeting invite in Outlook, or the file was already opened when I added it to the invite and began adding data shortly after; regardless it never saved, and when I finally closed the file and returned to it all of my updated data was gone. Hoping you can help me. :)
Thanks,
Karim

Type %temp% in the address bar of any My Computer or Windows Explorer Window and press Enter. This will but you into your Temporary Files folder. Outlook puts attachments that you open from an email in that folder. Search through and see if you can locate it. Beyond that I'm afraid I don't have any other advise. If you happen to find your file, please reply back so that others can benefit from your experience.

Thank you so much. That saved me a day's work!!!

Fantastic! Thank you for letting me know!

Did not know that there will be a option available in excel for recovering unsaved Excel Workbooks in Excel 2010 or later, Often most of the people lost their excel work because of software crash or un expected system shutdown but after adopting above solution they can now easily recover unsaved excel data in Excel 2010 or later version. If you face problem Excel file corruption in higher Excel Version then you can try this http://www.techrepublic.com/bl.... to overcome the problem.

many thanks..

I am happy I could help.

Is there any way to recover an excel file that hasn't been saved before. I saved it and it confirmed it was saved but when I went to look for it again Excel hadn't actually saved it...

I'm afraid that's highly doubtful. You can try searching your entire hard drive for the file name that you used, or type %temp% in the address bar of any My Computer or Explorer window and pick through your Temporary Files folder in hopes of finding it. In the Temporary Files folder it might have some gibberish name, so try to open anything that looks like an Excel file. Good luck!

I attempted to drag a file from one folder to another yesterday and somehow it disappeared. I've done file search to see if I might have dropped it into the wrong folder, but it doesn't turn up in a search. It still shows up in the Recent Workbooks list in Excel, but when I click on it I get "Excel cannot open the file because the file format or file extension is not valid." I'm at a loss ... any suggestions?

Moving a file outside of Excel has no impact on the Recent Workbooks listing, which only updates based on actions inside Excel. Check your Recycle Bin, as maybe you accidentally deleted it. Or if this file was on a network drive, your IT team may be able to restore a back-up for you. It's possible that file file has become corrupted. Now, for the file that's on your Recent Documents menu, if you hover your mouse over the file name Excel will show you the location of the file. Use the Open command to browse to that folder, and if the file is still there, click once on it, and then click the arrow next to the Open button and choose Open and Repair. That might enable you to recover your file. Good luck!

It wasn't in the Recycle Bin, or anywhere else that I could discover. Fortunately I was able to restore it from a back-up. I didn't know about the Open and Repair option, I want to remember that. I'm sure it will come in handy.

Whew! I'm glad you were able to recover your file. Open and Repair is one of my favorite hidden features in Excel. Thank you for taking the time to share your experience.This will probably be helpful to others in the future.

Just saved me 2 hours of work! And who knows how many in the future! Thanks! MS got it right!

Fantastic! I'm glad you ran across my article, and thank you for sharing your experience.

Another thing I just realized and was saved by actually. I didn't recover but choose to delete. Luckily, I just checked the recycle bin and the file was there be named along the lines of "[filename] version x"

Good call on checking the Recycle Bin. Only documents originating from your local hard drive will end up there, but it's definitely a good place to check.

yes! thanks.

Pages