Software

10 ways to recover a corrupted Excel workbook

When an Excel file goes haywire, don't give up hope. Using one of these recovery tricks, you may still be able to salvage the data.

When an Excel file goes haywire, don't give up hope. Using one of these recovery tricks, you may still be able to salvage the data.


Even if you faithfully back up your Excel workbooks, corruption can still be a problem. The backup files won't always contain your most recent work, so you'll probably end up re-entering data. Repairing a corrupted workbook, if possible, is a better option. In this respect, Excel can help. If you attempt to open a corrupted workbook, Excel will engage File Recovery mode, which attempts to repair the workbook. If that works, great! Unfortunately, Excel's automated File Recovery feature sometimes fails to repair a damaged workbook. When this happens, you'll need alternatives. Try the easiest solutions first. The more complex methods usually recover data, but no formulas, formatting, charts, or macros.

Note: This article is also available as a PDF download.

1: Let Excel try

If Excel's automatic recovery mode fails, there's a manual feature for recovering a corrupted workbook:

  1. Choose Open from the File menu. In Excel 2007, click the Office button and select Open.
  2. Using the Look In control, locate and specify the corrupted workbook.
  3. From the Open button's dropdown list, shown in Figure A, choose Open And Repair.
  4. If this is your first attempt to manually recover the workbook, click Repair in the resulting dialog box, shown in Figure B.

Figure A

Attempt to manually recover a corrupted workbook.

Figure B

You can repair a workbook or extract the data from a corrupted workbook.

If you're lucky, Excel will repair the file. However, this feature recovers files when something goes wrong with Excel, so it won't recover every corrupted file. Once you've made every attempt to recovery the file, you might have to make do with recovering just the data. When that's the case, click Extract Data in step 4.

2: If the corruption occurs while the workbook is open

If corruption occurs while the workbook is open, do not save the workbook. You'll just save whatever corrupted the workbook along with the most recent changes. Instead, revert to the last saved version. Doing so, without saving first should discard the corrupted component. You'll possibly lose data, but often, you can rebuild quickly enough (a good reason to save your work often).

To revert to the last saved version, do the following:

  1. Choose Open from the File menu. In Excel 2007, click the Office button and select Open.
  2. Using the Look In control, locate and specify the corrupted workbook.
  3. Click Open.

You're really just reopening the workbook -- there's nothing special going on.

3: Disable automatic calculation

If Excel fails to open the file on its own or via the Open And Repair option, try setting the calculation method to manual and try again. To reset the calculation setting:

  1. Open a new blank workbook.
  2. From the Tools menu, choose Options and click the Calculation tab. In Excel 2007, click the Office button, click Excel Options, and select Formulas in the panel to the left.
  3. In the Calculation section (Calculation Options in Excel 2007), click Manual.
  4. Click OK.

Then, try to open the corrupted workbook. Sometimes Excel can open a corrupted workbook if it doesn't have to recalculate.

4: Try Microsoft Office Tools

If Excel can't open the corrupted workbook, give Microsoft Office Tools a whirl:

  1. From the Start menu, choose All Programs (in Windows XP).
  2. Select Microsoft Office.
  3. Choose Microsoft Office Tools.
  4. Select Microsoft Office Application Recovery.
  5. In the resulting dialog box, shown in Figure C, choose Microsoft Office Excel.
  6. Click Recover Application. The process could take a few minutes.
  7. Respond to the Send Report To Microsoft prompt.

Figure C

Office Tools might recover a corrupted workbook.

The recovery tool will close Excel and then restart it, displaying a list of recovered workbooks (if any). If you're lucky, your corrupted workbook will be in the list. Simply open it and count your blessing.

5: Move the file

Sometimes, a corrupted workbook isn't really corrupted in the traditional sense. It just isn't accessible as usual; networks and servers often mask errors as corrupted files. If you encounter a seemingly corrupted workbook that Excel can't repair, move the corrupted file to another folder, drive, or server.

6: Let the competition try

Some people claim extraordinarily good results using OpenOffice Suite, a free open source alternative to Microsoft Office. This suite's Excel counterpart, Calc, should open a corrupted Excel file. Unfortunately, it means a lengthy download, but if it works, it's well worth the effort.

7: Open the corrupted workbook in WordPad or Word

If you can't repair the workbook, try opening it in WordPad. If it works, WordPad will convert everything to text -- but you'll be one step closer to recovering important data. Unfortunately, this method won't recover formulas. On the other hand, unlike many other data recovery methods, WordPad will recover your VBA procedures (macros). Simply search recovered text for Sub and Function to find them.

You might also be able to open the corrupted .xls file in Word, but the results will be limited. For this method to work, you must install the Microsoft Office Excel converter. And unlike WordPad, Word won't recover your procedures.

8: Use external references

Sometimes, you can recover data by referring to the actual cells in a corrupted workbook. You won't recover formulas, formats, charts, macros, and so on, but recovering the data is better than recovering nothing. To recover data by referencing the corrupt workbook, do the following:

  1. Open a new workbook and enter into cell A1 a formula in the following form to reference A1 in the corrupted workbook: nameofcorruptedworkbook!A1. (You don't need to include .xls in the filename.)
  2. Press Enter.
  3. If the corrupted workbook isn't in the same folder, Excel will display the Update Values: nameofcorruptedworkbook dialog box. Use the Look In control to locate the corrupted file. Select the file and click OK.
  4. If the Select Sheet dialog box appears, as shown in Figure D, select the appropriate sheet and click OK. Excel will display the value in cell A1 of the corrupted workbook.
  5. Select cell A1 and drag it across as many columns as needed to accommodate the data in the corrupted file. If Excel displays the Update Values: nameofcorruptedworkbook dialog box, select the corrupted file and click OK.
  6. Repeat step 5, copying row A down as many rows as necessary to accommodate the data in the corrupted file.
  7. Select the data and choose Copy from the Edit menu.
  8. Select Paste Special from the Edit menu and choose Values, as shown in Figure E.
  9. Click OK.

Figure D

If the corrupted file has multiple sheets, specify a sheet.

Figure E

Replace the referencing formulas with data.

9: Try SYLK format to recover data

Microsoft recommends using the SYLK format to filter out corrupted elements, especially if the corruption is printer related. You must be able to open the corrupted workbook for the following to work:

  1. From the File menu, choose Save As. In Excel 2007, click the Office button.
  2. From the Save As Type control, choose SYLK(Symbolic Link) (*.slk), as shown in Figure F.
  3. Give the active sheet a descriptive name, such as corruptedworkbooknameSheet1. If the workbook has only one sheet, this step is unnecessary.
  4. Click Save.
  5. If the workbook has multiple sheets, Excel will ask you if you want to continue because the selected format doesn't support multiple sheets. Click OK.
  6. If Excel prompts you with a warning that the workbook might contain features that aren't compatible with the SYLK format, click Yes.

Figure F

Save each sheet using the SYLK format.

It's important to know that the SYLK format saves only the active sheet. However, you won't notice that the format has stripped all of the pages but the active one until you close and reopen the .slk file. To recover data from all the sheets, you must reopen the corrupted workbook and save each sheet individually. That's why step 3 instructs you to give the sheet a descriptive name. You'll find those names helpful when reconstructing the multiple-sheet workbook.

After saving all the sheets to the SYLK format, open one of the .slk files and rename it using the .xls format. Be careful not to use the corrupted workbook's name. Once you've reconstructed the workbook, you can discard the corrupted file or rename it, freeing up the original name. Don't replace it until you're sure you've recovered as much data as possible. Then, reconstruct the workbook by importing or copying data from the .slk files. It's a tedious process, but worth the effort if nothing else works. This method saves values resulting from formulas, but not the formulas themselves.

10: Recover macros

If you can recover data but not macros via one of the previous tips, you can still save macros -- at least sometimes. To try, do the following:

  1. Open Excel, but don't open the corrupted workbook.
  2. Set the calculation mode to Manual (see #3).
  3. Choose Macro from the Tools menu, select Security, and choose the High option. In Excel 2007, click the Office button, click Excel Options, and choose Trust Center in the left panel. Then, click the Trust Center Settings button, select Macro Settings in the left panel, select Disable All Macros Without Notification in the Macro Settings section, and click OK twice.
  4. Open the corrupted workbook. If Excel opens the workbook, you'll see a notice that the macros are disabled. If Excel shuts down, this method won't work.
  5. Press [Alt]+[F11] to open the Visual Basic Editor (VBE).
  6. Using the Project Explorer (press [Ctrl]+R), right-click a module, and choose Export File.
  7. Enter a name and folder for the module.
  8. Repeat steps 6 and 7 as many times as necessary to export all the modules.
  9. Close the VBE and exit Excel.
  10. Open a new blank workbook (or the newly constructed workbook that contains recovered data from the corrupted workbook) and import the modules.


Corrupted Word doc?

If you run into problems with a Word document, check out these recovery strategies.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

15 comments
jenniferlizit
jenniferlizit

Excel Repair Toolbox provides automatic recovery for a corrupted .xls/.xlsx/.xlm/.xlmx files in a few clicks. This product provided me unbelievable results in recovering a complex spreadsheet that had over 3 years of data included. I spent the last 12 hours messing around with a variety of tools across the internet with no luck. I was facing corruption errors, file would not open, etc. This tool within minutes fixed the spreadsheet problems and I now have my recovered spreadsheet.  http://www.excel.repairtoolbox.com/

Diane Kjorven
Diane Kjorven

8:  Use external references actually worked to help me recover a year's worth of entries from 10/11! Yay!

Unfortunately, the entries I did after that appear to be AWOL!!  

vanirig
vanirig

Try there're soft: Active File Recovery, Recover My Files.

http://www.excel.repairtoolbox.com/

Also a demo version of Excel Repair Toolbox to find a lost data from .xls, .xlsx files. In a cases when the build-in recovery function in Microsoft Excel are not helpful then you can use most effective third-part tool for fixing a bad Excel worksheets: Excel Repair Toolbox. 

mac.john
mac.john

I read you posting But am not sure It's work or not  to recover a corrupted Excel file by manually. I recommend 3rd party tool called "Kernel for excel recovery". Software help to easily recover damaged Excel file and Repairs the both .XLS and .XLSX files formats of MS Excel

ahmadh2o
ahmadh2o

Funny enough the only way i could get a MS Excel 2007 currupted sheet to open was to open using LibrOffice3 , this saved the day of an important user and she was really happy about it. heads-up for microsoft :)

Claptrap1
Claptrap1

One thing that managed to open my MS Word file when everything else failed was Open Office, it saved my life! Maybe will work with Excell as well, no harm of keeping a copy of this free office package.

DamnedFreak
DamnedFreak

Open the spreadsheet with OpenOffice. It is way more forgiving with corrupted files. Once opened in OpenOffice, save a new copy of the sheet with the "Save as" function. Helps me in more than 50% of all corruption cases.

seanferd
seanferd

Corruption is bound to happen eventually, and you have provided some excellent recovery options.

Michael Kassner
Michael Kassner

Thank you. I know there is a lot of work in that piece. I appreciate your effort and all the great ideas.

Ed Woychowsky
Ed Woychowsky

I've had luck with number 6, using Open Office. From personal experience I know that it also works with Power Point. Oh well, you can't have everything!

ssharkins
ssharkins

...that none of you ever need these tips! ;)

ssharkins
ssharkins

What a nice thing to say -- I really appreciate it.