Troubleshoot Your Workbook in Microsoft Excel

Microsoft Office has what I call containers. Workbooks, databases, documents...when they go corrupt, it seems it's the container that seems to go bad. If you move the objects into a new container, all seems well again.

This often helps to remove file bloating and crashing.

If you have a version that supports it, you can first try the Repair utility. Go to File Open, and select (but don't open) your file. On the bottom-right, click the drop-down next to Open and choose Open and Repair.

If that doesn't work or you don't have a version that provides it, you can move your workbook into a new container by copying the sheets into a new workbook following the instructions here. If you have many worksheets, or worksheets that contain a lot of data, you may want to copy only one or two sheets at a time, saving the new workbook each time. This is often a good way to find a corrupt worksheet because Excel may crash when you try copying it to a new book.

If you must retrieve your data from a worksheet that simply crashes Excel and it becomes impossible to copy the data, you may still be able to retrieve it.

Suppose we have a file called mydata.xls. We have managed to copy sheets 1 and 2 over to a new file already, but Sheet3 crashes on us. Copy cell A1 from Sheet2 and paste it as a link into Sheet3, cell A1 of your new workbook. The pasted link looks like this:

Change the 2 in Sheet2 to a 3. Copy the formula right and down as many columns and rows as you think necessary to pull the data over from the corrupt worksheet. When you're done, select all the formulas and copy them, then Edit Paste special as Values. Hit OK.

If you cannot even get your workbook open, or if none of the above is helpful to you, then try opening your workbook using OpenOffice, which you can download free. When you install OpenOffice, be sure not to let it take over your Office file extensions. There's nothing special about OpenOffice that it can (likely) open your corrupt workbook, it's just that it may not see the corruption. Lotus 1-2-3 could work just as well, but it's not free.

I have found 3rd-party "recovery" tools lacking. I never found one I thought was worth the money.