Excel Document Management Tips and Tricks

Recovering Unsaved Workbooks

You’ve likely experienced that flash of panic that occurs when suddenly the Excel spreadsheet you’ve been laboring over unexpectedly vanishes off of your screen. I’ve certainly gotten lost in my work innumerable times over the years and forgotten to save periodically. This often sets the stage for any of a number of bad things to happen: your computer crashes; the power goes out; you close the wrong workbook without saving—the list goes on and on. Fortunately in Excel 2010 and later you have pretty good odds of being able to recover your work.

Get the full Excel Document Management CPE course

 

 

Microsoft Excel has long had a Document Recovery feature that in many cases will present a list of workbooks that were open at the time that Excel crashes unexpectedly. Ostensibly Excel creates a back-up copy of your document every 10 minutes, and it’s these back-up files that are presented to you after a crash. However, if you close the Document Recovery window without making a selection, those back-up files are lost forever. Fortunately in Excel 2010 and later you have a couple of additional recovery options:

• Follow the steps shown in Figure 1.
• Click File, Info, and then Manage Workbook in Excel 2016 (or Manage Versions in Excel 2010/2013), and then Recover Unsaved Workbooks.

In either case an Open Dialog box will appear from which you can choose any workbooks that Excel has saved copies of on your behalf. Excel removes these files after a couple of days.

Figure 1: Select File / Open / Recover Unsaved WorkBooks

There’s still more to learn about this feature in Excel, including how to have Excel create back-up copies on a more frequent basis. The one-hour text-and-video course accompanying this article goes explores the Unsaved Documents capability in more detail, along with many other document aspects, such as managing protected view, creating an automatic back-up of specific spreadsheets, streamlining access to files and folders, and faster ways to convert spreadsheets to PDF format.

Repairing Damaged Workbooks

It can be soul-crushing when you’re going about your day and instead of a spreadsheet appearing onscreen for you to continue your work you encounter a prompt that informs you that the workbook is damaged and cannot be opened. Although this can be an unsettling experience, all isn’t necessarily lost. You may automatically encounter a prompt that informs you that the workbook is damaged that includes options to Repair or Extract Data, but you can also access this feature on your own, as shown in Figure 2.

Figure 2: Choose Repair

As shown in Figure 2, the first and best option is to choose Repair, which will recover as much data from the workbook as possible, including formatting. If that recovery attempt is unsuccessful, repeat steps 1 through 6, and then choose Extract Data, which will to recover only values and formulas from the workbook. In this case you’d have to apply any fonts, number formatting, colors, and so on again but you may be able recover the most important parts of your document.

Get the full Excel Document Management CPE course.

EARN CPE