Author/Presenter/Spreadsheet Consultant Accounting Advisors, Inc.
Columnist
Share this content
excelrecover
mediaphotos/iStock

Recovering Unsaved Excel Workbooks

by
Mar 15th 2013
Author/Presenter/Spreadsheet Consultant Accounting Advisors, Inc.
Columnist
Share this content

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 [email protected]. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Tags:

Replies (137)

Please login or register to join the discussion.

avatar
By chacha
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Kali Khams:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

You're welcome! I appreciate the feedback.

Thanks (0)
avatar
By daragh
Jun 25th 2015 20:11 EDT

THANK YOU!!!

Thanks (0)
Replying to cip:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

My pleasure, thanks for reading.

Thanks (0)
avatar
By AbuSaarah
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to thomasaveryblairea:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By Jess
Jun 25th 2015 20:11 EDT

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.

Thanks (0)
Replying to primeoutsourcing:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By tcortel
Jun 25th 2015 20:11 EDT

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?

Thanks (0)
Replying to JFallsChurch:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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.

Thanks (0)
avatar
By tiffanyima
Jun 25th 2015 20:11 EDT

I could kiss you. Saved hours of work!

Thanks (0)
Replying to Blair Illiano:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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!

Thanks (0)
Replying to Eric Meury:
avatar
By tiffanyima
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Sandi Smith Leyva:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By j
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Elenacas:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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.

Thanks (0)
avatar
By liu
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to cookiect:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By FU
Jun 25th 2015 20:11 EDT

CAN'T READ WITH AD BLOCKING.

Thanks (0)
avatar
By justme
Jun 25th 2015 20:11 EDT

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.

Thanks (0)
Replying to Boonies:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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.

Thanks (0)
Replying to Kmarsh:
avatar
By Justme
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Rae:
avatar
By Justme
Jun 25th 2015 20:11 EDT

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!!

Thanks (0)
Replying to SullyMR:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By Amanda
Jun 25th 2015 20:11 EDT

Thank You. Thank You!!! God Bless you. You saved my [***]..

Thanks (0)
Replying to Jeffrey G:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

My pleasure!

Thanks (0)
avatar
By Elaine
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Scott H Cytron:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By Suzanne
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Yogi:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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!

Thanks (0)
avatar
By help
Jun 25th 2015 20:11 EDT

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?

Thanks (0)
Replying to Ohab Company:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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 (0)
avatar
By Ted
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Joyce Miller:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By Chris
Jun 25th 2015 20:11 EDT

This is great information! Thanks!!!

Thanks (0)
Replying to Daniel:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By Kitty
Jun 25th 2015 20:11 EDT

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!

Thanks (0)
Replying to DanaBee:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By Jann
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Doug:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

Magnificent!

Thanks (0)
avatar
By John
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Guest:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By caswalmac
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Jan:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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

Thanks (0)
Replying to Jan:
avatar
By Amit Sharma
Jun 25th 2015 20:11 EDT

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/

Thanks (0)
avatar
By Denis Frith
Jun 25th 2015 20:11 EDT

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?

Thanks (0)
Replying to Reef:
avatar
By David Ringstrom
Jun 25th 2015 20:11 EDT

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.

Thanks (0)
Replying to Ellen Finkelstein:
avatar
By Denis Frith
Jun 25th 2015 20:11 EDT

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.

Thanks (0)
avatar
By jack
Jun 25th 2015 20:11 EDT

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

Thanks (0)
avatar
By Lissa White
Jun 25th 2015 20:11 EDT

I started a worksheet in Excel 2013 and then emailed it to myself for later work. I opened the document from my gmail and was working on it Excel 2010. I thought I was saving the document to my computer- I renamed it and was hitting the save icon periodically - but today I cannot locate it anywhere on my computer. Any thoughts or suggestions?

Thanks (0)

Pages