It’s often a user’s worst nightmare to find that an important workbook suddenly won’t open, mostly due to the fact that such files are irretrievably corrupted, but there are a few steps you can take before you decide to start over from scratch with your spreadsheet.
Excel generally launches File Recovery mode when a workbook cannot be opened cleanly. This consists of the prompt shown in step 4 of Figure 1. However, you can also manually launch File Recovery Mode for any spreadsheet that you wish, as shown in Figure 1:
Click once on the affected file within Excel’s Open dialog box.
Click the arrow at the right-hand side of the Open button to display a menu.
Choose Open and Repair.
Figure 1: The hidden Open and Repair command can help you attempt to recover damaged workbooks.
No matter how File Recovery mode is triggered, always try the Repair option first. Many corruption issues tend to be formatting related, and you may not even notice that anything has changed about the spreadsheet. If this option doesn’t work, next try the Extract Data option. This option strips all formatting out of your spreadsheet, such as fonts, borders, and so on, but attempts to keep formulas and data.
An alternative to File Recovery mode is to set Excel to Manual Calculation before attempting to open the damaged workbook. To do so, choose Manual from the Calculation Options command on Excel’s Formulas menu before you attempt to open the damaged workbook. If the file still won’t open for you, the next step to try is to check for previous versions of the file, as shown in Figure 2:
Right-click on the affected file name within Excel’s Open dialog box.
Choose Restore Previous Versions.
The Previous Versions menu will indicate if any previous versions are available by way of Windows Back-Up.
Figure 2: The Restore Previous Versions command will allow you to check for previous versions of the file.
If you are using a file-hosting service, such as Dropbox or Google Docs, or an online backup service, such as Carbonite, you may be able to recover a previous version from there. However, don’t give up hope if neither of these options work. Excel for Mac is sometimes able to open workbooks that Excel for Windows won’t. Another option is install the free Libre Office suite (www.libreoffice.com). The Calc spreadsheet within this suite can sometimes open corrupted Excel workbooks.
One final option involves attempting to rebuild the workbook. Note that this will only bring over static data, without formulas or formatting, but it may give you a jump start on rebuilding your spreadsheet. As shown in Figure 3:
Type the following formula in cell A1 of a blank workbook:
Note that you don’t need to include the file extension, just the name, such as Financial Statement surrounded by single quotes.
Press Enter to store the formula in the cell.
If your workbook has a single worksheet, the contents of cell A1 should appear. Otherwise a Select Sheet dialog box will prompt you for the worksheet to use.
Click OK if needed to close the SelectSheet dialog box.
You can now drag the formula down as many rows and across as many columns as needed.
Figure 3: Use a formula in a blank workbook to link to the workbook you’re trying to reconstruct.
Repeat those steps for each worksheet in the workbook. You can copy the resulting formulas from the clipboard and then use the PasteSpecial command to paste as values. You’ll hopefully have a foundation to work from if you must start your document over.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.