Recovering Unsaved Excel Workbooks

Columnist
Share this content
Tags
171

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.

thanks alot !!!!!!!!!!!11
very helpful

You're welcome! I appreciate the feedback.

THANK YOU!!!

My pleasure, thanks for reading.

Thanks a lot....
It saved my 2 hours work... I just closed the file mistakenly without saving...

I'm glad my work helped you avoid redoing your spreadsheet!

Thank You!! I clicked no to save a document that had never been saved before. There wasn't even a starter file to go back to. But I followed your steps to the Manage Version tab right after I closed out and there it was, temporarily waiting for me to save it! Thanks so much.

I'm so glad. Thank you for sharing your experience!

What about a file from a few days ago? I worked on a file 9 days ago. printed it and didn't think I would need it again but now I do. Any advice?

I'm sorry to be the bearer of bad news, but I think you'll have to recreate your file again. From what I've observed, unsaved workbooks linger for a couple of days, and then get automatically deleted. I've never stopped to try to figure out exactly how many days a file stays around, but it appears to be 3 or 4.

I could kiss you. Saved hours of work!

I must say, that's the most fun response I've ever gotten to one of my articles! I'm so glad that you found it helpful!

Haha I realize now that was a very awkward response but literally saved so much time

No worries, I'm glad you felt comfortable to speak freely. :-)

i recovered the files but the pictures attached inside are all missing

It's hard to diagnose exactly what happened, but one thing that comes to mind is that you could have added the pictures subsequent to when Excel last created the AutoRecover version. I don't typically incorporate pictures into my spreadsheets, so I can't attest to how well the AutoRecover feature preserves the fidelity of such documents. For what it's worth, It's always worked well for me with my documents.

I almost had a heart attack , thanks a lot :)

Whew! I'm glad you found my article! Thank you for the feedback.

CAN'T READ WITH AD BLOCKING.

How about if excel starts "not responding"? All of a sudden, a "buffering" symbol started appearing and it has not gone away. I am not sure how soon you will see this but i am not sure whether to close the document or continue waiting or shut down the computer to later trigger the autorecovery feature.

I'd say at this point you're best off trying to force-close Excel and hope that it brings back an auto-recover version. Be sure to change the setting I mention to every 2 minutes down from the default of 10 minutes to vastly improve the odds of having an autorecover version in the future.

Thank you so much!! I had no idea about these features. Very helpful!

It worked! yes..I shut down the computer and it forceclosed everything. Oddly enough, I was able to open another excel document while that document was still "not responding". But I just did the forceclose and it worked. Thank you!!

Fantastic! Thank you for sharing your experience, this will help others that happen across this article.

Thank You. Thank You!!! God Bless you. You saved my butt..

My pleasure!

You've just saved us from having to redo an hour and a half's work. Thank you!

You're quite welcome. I'm glad you were able to find my article.

Hi worked on a spreadsheet all day yesterday Autosave was on and still I lost all the data. It is saying there are no previous versions available. I work on this type of file everyday and never had problems. Its so urgent can anyone help. I cannot find a temp file either

It's unfortunate that you spent so much time on your document without saving it. A good habit to develop is periodically pressing Ctrl-S in Excel to save your work as you go.

The only other option I can suggest to you at this point is to use Windows Explorer to navigate to the folder where your file resided. Right-click on the file, and see if a Previous Versions command appears. If so, you might get lucky, otherwise you'll have to redo your work, which is a lesson I had to learn myself the hard way multiple times over the years. Good luck!

Hi! i worked on a spreadsheet for two hours yesterday and thought i saved it. Every time i worked on this document it would creat an autosaved copy. i turned off my computer and the restore screen came up on the left but all of the autosavedcopies were there. I thought i chose to save the correct one and deleted the rest, but i picked the wrong one. I found it in my trash can and hit restore, but now i can't find it! any ideas?

I'm sad to report that you've most likely lost your work. AutoSave is an imperfect feature that does not in any way replace the necessary discipline of periodically saving your documents.

Thanks for the info, save me hours of rebuilt.....

Whew! I'm glad you recovered gracefully! Thanks for sharing your experience.

This is great information! Thanks!!!

Thank you! I'm glad you found it helpful.

THANK YOU!!!! I had even called our IT Help Desk about this and they told me there was nothing they could do. After reading this and a couple of clicks, I found my file!

Fantastic! Please be sure to pass the article along to your help desk folks. :-)

Super helpful article! Thank you so much! Saved me at least 4 hours of re-work.

Magnificent!

Awesome, I was about to have a heart attack. Saved.

I'm glad I could save the day for you. Thanks for the feedback!

My excel doesn't look like this! There's no "File" I don't know what to do!!

You're using Excel 2007, and as noted above, the Recover Unsaved Workbooks feature was introduced in Excel 2010.

If you are using excel 2007 in that case you have to activate Auto Recovery option from Office button -> Excel Options -> Click Save tab -> Active AutoRecover information in every 10 minutes. I hope you like this answer. You can also logon on http://www.eknowledges.com/

I did not save an Excel2010 workbook yesterday. I tried to recover it using the procedure above. When I clicked on 'recover unsaved workbooks' a window asked for the workbook name. A message came up saying it could not be found. What did I do wrong?

I don't think you did anything wrong per se. I haven't experienced the prompt that you encountered, and so unfortunately I can't diagnose what you experienced. It could be that one of the spreadsheets in the AutoRecover folder was corrupted, again it's hard to say without being there.

As I was unable to recover the unsaved version, I have redone the editing that was lost. That took some hours! So now I will be more careful by saving after an edit. Thank you for considering my problem.

I'm sorry to hear that. I've been in your position before. Do be sure to change the AutoRecover setting to every 2 minutes (down from the default of 10) to help raise the odds of having an AutoRecover version in the future.

why the f*****g s**t do we have to log in the read this bloody article???

Pages