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
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
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.
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved