A corrupted Excel file can ruin many hours of work. However, the program has a number of features that minimize the chances of this happening. One of the reasons that lead Microsoft to change Excel's file format beginning with Excel 2007 was to reduce the chance of file corruption rendering your document completely unrecoverable. In addition, if corruption does happen, you still have some options for recovering your data.
Unless you've turned the feature off, Excel constantly saves a working file after a few seconds without keyboard input. If Excel shuts down unexpectedly, you may see a pane on the left side of the window giving you a choice of file versions to open; select one to see if Excel's auto-recovery feature saved a version of the file before the error occurred. If this pane doesn't show up, click "File," select "Open," click the "Look In" control, and then find the corrupted workbook. Select the arrow next to the "Open" button, click "Open and Repair," and then click "Repair" on the resulting dialog box. You may also want to turn off Autocalculate in the Excel Options pane before attempting to repair the file.
Using Microsoft Office Tools
Microsoft provides a set of additional tools in Office, including some data extraction and file recovery tools. Pull up the Search charm, search for "Microsoft Office Tools" among Apps. Launch the application, select "Microsoft Office Application Recovery," choose "Microsoft Office Excel," and then click "Recover Application." If Excel is open, it shuts down and restarts with a list of recoverable workbooks. Look for the corrupted workbook on the list. While this is a slightly more laborious method of recovering your document than the Auto-Recovery method, you can find much older versions of files than you might see on Excel's Auto-Recovery pane.
Check File Locations
You may sometimes get a file corruption error because Excel can't read -- or sometimes, write to -- the drive the file is on. Fix this type of file error by copying the file to a new location.
Use Another Program
You can sometimes recover corrupted Excel files by opening them in OpenOffice or LibreOffice. This was a tried and true method of recovering older Excel format files, but it still works with Excel 2007 and later versions. You can also try opening the file in WordPad or Microsoft Word as a last-ditch effort. This won't recover Excel formulas, but will recover the contents in a spreadsheet. With WordPad, you can also recover any macros in the resulting text file -- search for the terms "Sub" and "Function" to find them.
Pulling Data Through Other Spreadsheets
A particularly clever way to pull data out of a corrupted worksheet is to use Excel's ability to link worksheets together to pull data from a spreadsheet you can't otherwise open. Go to cell A1 of the new worksheet, type "=" (omit the quotes), the name of the corrupted worksheet, followed by an exclamation point, and then "A1".
The entire formula looks something like this:
Press "Enter" to have the formula evaluate. Excel may ask you to locate the corrupted worksheet if it's not in the same directory, and may also ask you to select which tab of the corrupted worksheet to use; select the appropriate worksheet and tab, if needed. Cell A1 of the new worksheet now shows the contents of cell A1 from the corrupted worksheet; recover the other data in that worksheet by copying and pasting the formula you entered in cell A1 to the other cells of the new worksheet. Note that this only recovers the contents of the cells, not any formulas or macros.
- Jupiterimages/Photos.com/Getty Images