Unfortunately anyone who works regularly with Excel will have come across the situation of a corrupt Excel file. The problem is that trying to repair corrupt Excel files is more an art than a science. Below we have itemised the steps we follow.
YouTube Open and Repair Corrupt Excel Files
Open with different versions of Excel
We recently came across a situation where the file opened in Excel 2010 but would not even open in Excel 2013. After a bit of work and some of the tricks mentioned below it would open in Excel 2013, but until then it crashed the program
Open and Repair
You don’t often notice it but when you open a file there are more options then just clicking OPEN. If you click on the drop down arrow shown below you will see that you can open in a number of different ways (you can see more at Excel Open As option post ).
In this case you can try the Open and Repair option. When you click this you will get an option to Repair or to Extract data. Generally you will try the repair first (it attempts to repair and recover as much of the file as possible).
If this didn’t work, then you can try the Extract Data which attempts to get your data out and put it into a new workbook. You get asked another question with regards how much of the formula it should try and extract. Depending on the file you may choose either.
There is more information on what you can try and do to repair files on the Microsoft Support site
Extract info via a external link
In desperate times you may try and create a new workbook and set up a formula to go into the file and extract the contents of all the cells.
Remove Logos, Images and Pictures
If any of these exist in the file, they may have some links or connections that you don’t know about. Delete these and see if it helps
Check the Named Ranges
Named ranges sometimes contain links you are unaware of and that can cause problems. Go to the Formula’s Tab and click on Name Manager. See if there are any named ranges you can delete
Inspect document tool
In the newer versions of Excel, there is an Inspect Workbook tool. This tool runs a number of checks. Although the focus seems to be on privacy i.e. not sending a document with confidential information hidden somewhere, it is useful to identify possible reasons that the file is corrupting
The issues it looks for are:
- comments and annotations,
- personal information in the document properties,
- data model hidden data,
- content apps,
- task pane apps,
- pivot table related issues,
- embedded documents,
- macros, forms and activeX controls,
- links to other files,
- real time data function,
- excel surveys,
- scenarios,
- active filters,
- custom worksheet properties,
- hidden names,
- custom XML data,
- headers and footers,
- hidden rows and columns,
- hidden worksheet and
- invisible content