What Is Excel File Corruption?
It is when contents inside an Excel workbook go haywire. Microsoft doesn't offer a manual on Excel file corruption. A workbook can be damaged, if improperly manipulated, by any application which reads/writes OpenXML files.
Developers usually experiment using a trial-and-error approach, while trying to repair a file or salvage its contents. Excel file corruption isn't new. It dates back to Excel origins: MS-Article ID: 213951
Developers usually experiment using a trial-and-error approach, while trying to repair a file or salvage its contents. Excel file corruption isn't new. It dates back to Excel origins: MS-Article ID: 213951
Why should you bother to learn about corruption in Excel?
If you are an experienced Excel user or developer, sooner of later, you will try to open a workbook that has been corrupted.
If you are an experienced Excel user or developer, sooner of later, you will try to open a workbook that has been corrupted.
- Learn to identify file corruption and avoid getting confused by the odd warnings:
If a user opens a corrupted workbook in Excel 2013, while being oblivious that the file is damaged, the malfunction could be attributed to VBA incompatibility between Office versions. As a result the user may try to troubleshoot VBA code that isn't 'broken' in the first place !
- Learn what the Excel repair process will do to your workbook:
As discussed at the end of article, the behavior of a repaired workbook may prove unpredictable, as any Excel content may be removed randomly by Excel's internal repair mechanism or even remain damaged after several repair attempts by Excel.
A lot of users feel confident that a repaired file is 100% identical to the original, but unfortunately it won't be!
How To Detect Excel File Corruption
1) Lingering but undetected: Unfortunately, file corruption might not always be detected with the MS-Office version you are working with. It may sound strange at first, but that's actually good news. A workbook, which cannot be opened in any Excel version is usually a hopeless case.
We have repaired successfully corruption issues in workbooks, which could be opened in Excel 2003 & 2007, but not in Excel 2010. Please read our case study towards the end of this article.
2) Detected on file open: The majority of corrupted files are detected, while Excel attempts to open a file. Excel will display a corruption & repair warning:
We have repaired successfully corruption issues in workbooks, which could be opened in Excel 2003 & 2007, but not in Excel 2010. Please read our case study towards the end of this article.
2) Detected on file open: The majority of corrupted files are detected, while Excel attempts to open a file. Excel will display a corruption & repair warning:
- Excel 2007/2010: "Excel found unreadable content in <filename>". Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes"
- Excel 2013: "We found a problem with some content in <filename>. Do you want us to try recover as much as we can? If you trust the source of this workbook, click Yes"
- "Excel cannot open the file <filename>", because the file format or file extension is not valid. verify that the file has not been corrupted and that the file extension matches the format of the file.
- The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt.
- "Can't find project or library. Microsoft Excel is restarting..."
- Multiple custom UI parts were found in the file. Only one part is expected.
An 'apocalyptic' message displayed by Excel 2007/2010 while opening a corrupted workbook.
A file corruption warning as shown in Excel 2013
'Fatal' corruption warnings in Excel 2010: Excel cannot even open the corrupted file.
3) Detected in an Active Workbook: Even the workbook you are working with, can be corrupted e.g. while it is loaded in RAM memory. The first tell-tale sign would be a warning from AutoRecover, if it is enabled.
Excel's AutoRecover will fail to save a valid file, while attempting to create a copy of your workbook on disk every couple of minutes (depends on user settings under Excel Options/Save). Besides corruption, an AutoRecover error may occur due to any disk write error, so your next step should be to save your active workbook.
Unfortunately, a corrupted workbook loaded in memory cannot be saved intact. Excel may warn you that:
Excel's AutoRecover will fail to save a valid file, while attempting to create a copy of your workbook on disk every couple of minutes (depends on user settings under Excel Options/Save). Besides corruption, an AutoRecover error may occur due to any disk write error, so your next step should be to save your active workbook.
Unfortunately, a corrupted workbook loaded in memory cannot be saved intact. Excel may warn you that:
- "An unexpected error has occured. Autorecover has been disabled for this session of Excel."
- "Errors were detected while saving <filename>. Microsoft Excel may be able to save the file by removing or repairing some features.. To make repairs in a new file, click continue."
- "Excel encountered errors during save. Excel was able to minimally save your file to <filename>"
A corrupted workbook cannot be saved automatically, so AutoRecover has been disabled in Excel 2010
Excel 2010 corruption warnings, while user tries to save an open workbook
During workbook save, Office will detect, if image relationships in the customUI XML part have been corrupted inside an OpenXML file. Even, if just a single image is to blame, Office will remove all images from the customUI part and reset relationships to default.
Unfortunately, Office doesn't display any warning for this repair activity during Save/SaveAs, even if 'UI errors' have been enabled in Excel Options (Advanced tab). So you may get taken by surprise when you discover that all images stored inside your workbook have disappeared without a hint!
Unfortunately, Office doesn't display any warning for this repair activity during Save/SaveAs, even if 'UI errors' have been enabled in Excel Options (Advanced tab). So you may get taken by surprise when you discover that all images stored inside your workbook have disappeared without a hint!
4) Excel crashing randomly: The dreaded warning every Excel user fears: "Excel has stopped working ..." or in layman terms "Excel has crashed". Your workbook may be partially or entirely recovered, based on your AutoRecover settings, assuming it has been enabled.
We have witnessed client workbooks crashing randomly. These workbooks were usually created in Excel 2003, migrated and repaired in 2007/2010. Unfortunately, Excel's default repair mechanism may not be 100% successful and bits of corrupted material could still linger inside a previously corrupted file and trigger Excel crashes randomly.
Needless to say, that NOT all Excel workbooks crash due to file corruption. Some could crash due to Excel bugs in combination with VBA code that doesn't follow best development practices. Improper use of codenames in VBA can also crash Excel.
In addition, workbooks may crash, if your Office version has not been patched properly. For more information about Office Service Packs, please read: How to diagnose & stop crashes in Excel.
We have witnessed client workbooks crashing randomly. These workbooks were usually created in Excel 2003, migrated and repaired in 2007/2010. Unfortunately, Excel's default repair mechanism may not be 100% successful and bits of corrupted material could still linger inside a previously corrupted file and trigger Excel crashes randomly.
Needless to say, that NOT all Excel workbooks crash due to file corruption. Some could crash due to Excel bugs in combination with VBA code that doesn't follow best development practices. Improper use of codenames in VBA can also crash Excel.
In addition, workbooks may crash, if your Office version has not been patched properly. For more information about Office Service Packs, please read: How to diagnose & stop crashes in Excel.
|
|
Corruption Hint: Excel crashes after reporting that a project or library cannot be found.
However, there are no missing library references or VBA projects!
However, there are no missing library references or VBA projects!
|
|
Press DEBUG: The above message is not very informative for the an Excel user!
Corruption Case Study
A workbook template, which was created in Excel 2003 and was later converted to 2007 .xlsm format, was sent to us by Gary. As such, it contained no data. The workbook run without any issue in both Excel 2003 & 2007, but wouldn't even open in Excel 2010.
Gary wrote: "I’d rather not have to rebuild all my templates in native 2010, as that just seems a waste of time and there will still be existing workbooks to deal with. The degree of error appears to vary from file to file:
The MS recover process will try to correct the file a certain number of times. So just taking the template you have, and workbooks created from that template, I have some that will go through the recovery process 6 times and then finally open and others that will do 15 'recovers' and then give up. Some will show errors on sheets that no longer exist..."
Analysis: The workbook template contained over 70,000 formulae in several sheets with a complex layout, so a complete rebuilt would be quite time consuming. In addition, data would have to be transferred from several populated workbooks to the new template.
The workbook was analyzed thoroughly using our custom Excel/VBA tools and several issues triggering corruption in Excel 2010 were identified. We were able to repair the workbook in Excel 2007 using VBA code alone !
Gary promptly confirmed our results by repairing numerous workbooks in both U.K./USA using our process and without losing any content !
We decided to test, if claims by commercial corruption recovery software / utilities are valid. Unfortunately, all software tested failed to restore Gary's template to a working state and couldn't recover any data or formulas.
We maintain a sceptical approach towards commercial recovery software and its potential for success, when migrating corrupted files from Excel 2003 to 2007/2010.
Gary wrote: "I’d rather not have to rebuild all my templates in native 2010, as that just seems a waste of time and there will still be existing workbooks to deal with. The degree of error appears to vary from file to file:
The MS recover process will try to correct the file a certain number of times. So just taking the template you have, and workbooks created from that template, I have some that will go through the recovery process 6 times and then finally open and others that will do 15 'recovers' and then give up. Some will show errors on sheets that no longer exist..."
Analysis: The workbook template contained over 70,000 formulae in several sheets with a complex layout, so a complete rebuilt would be quite time consuming. In addition, data would have to be transferred from several populated workbooks to the new template.
The workbook was analyzed thoroughly using our custom Excel/VBA tools and several issues triggering corruption in Excel 2010 were identified. We were able to repair the workbook in Excel 2007 using VBA code alone !
Gary promptly confirmed our results by repairing numerous workbooks in both U.K./USA using our process and without losing any content !
We decided to test, if claims by commercial corruption recovery software / utilities are valid. Unfortunately, all software tested failed to restore Gary's template to a working state and couldn't recover any data or formulas.
We maintain a sceptical approach towards commercial recovery software and its potential for success, when migrating corrupted files from Excel 2003 to 2007/2010.
Corruption recovery software/utilities mis-diagnosed Gary's template and failed to recover any content
"Petros' help was invaluable during our conversion from Office 2007 to 2010. Faced with a hidden problem whereby of one of our key Excel templates would run in 2007 but not even open in 2010, I was faced with having to rebuild the tool.
Petros was able to find an obscure problem that 2007 ignored and did not overtly manifest itself in the document. His insight saved me a lot of work in the long run for which I am very grateful."
Gary W., Merchandise Systems Analyst at diversified multinational mass media corporation
How To Repair A Corrupted Ribbon UI Customization
Custom UI Error: "Multiple custom UI parts were found in the file. Only one part is expected"
The above warning indicates that the custom Ribbon UI has been corrupted. Excel cannot repair this type of corruption. The node highlighted below (Target="NULL") has to be deleted in order to repair the file.
The above warning indicates that the custom Ribbon UI has been corrupted. Excel cannot repair this type of corruption. The node highlighted below (Target="NULL") has to be deleted in order to repair the file.
How Excel's Repair Mechanism Works
If Excel file corruption is detected as described above, please do NOT rush to recover the file. File recovery by Excel will result in some type of content to be deleted in most cases when the message "Excel was able to open the file by repairing or removing unreadable content"
There 2 problems with Excel's recovery process:
There 2 problems with Excel's recovery process:
- You don't know what content was deleted in detail. Warning: Formulas could be removed and replaced by values.
- The workbook could still be unstable or prone to corruption, even if Excel has completed one or more repair attempts.
Any type of content could be deleted (formulas, shapes etc), while Excel attempts to recover a corrupted workbook
A rather more safe course of action:
- Click 'No'
- Close all other open Excel workbooks.
- Open the most recent backup copy of the workbook and enter any missing data or rebuild features since the last backup.
- Make a copy of the corrupted file or ensure that you have already one.
- Do not overwrite the original corrupted file with the recovered version, if you have accidentally attempted recovery. Save with a new filename instead.
- Do not attempt DIY repairs based on YouTube videos or unreliable online Excel articles. We have received files damaged by clients beyond repair due to irresponsible file manipulation.
How To Remove Corrupted Styles
Sometimes a huge number of styles (magnitude of tens of thousands) can be built over time inside workbooks used by a large number of users. A quick-and-dirty trick to restore Excel's 2013 default styles is to delete the styles.xml from any OpenXML file.
Unless you come across the "Unrecoverable Styles Corruption" warning, this method is not normally recommended, as besides redundant unused styles, any custom styles that are in use will be deleted as well.
While opening the file, Excel will detect that styles.xml is missing and report the workbook as corrupted. The styles.xml part will be created with a fresh copy of built-in styles, after the workbook is repaired. Please don't forget to save the repaired file.
Unless you come across the "Unrecoverable Styles Corruption" warning, this method is not normally recommended, as besides redundant unused styles, any custom styles that are in use will be deleted as well.
While opening the file, Excel will detect that styles.xml is missing and report the workbook as corrupted. The styles.xml part will be created with a fresh copy of built-in styles, after the workbook is repaired. Please don't forget to save the repaired file.
Known Issues That Trigger Workbook Corruption
- A workbook may become corrupted, if there are many merged cells and the user sorts the range: Bug fixed with Excel 2013 Service Pack 1
- A comma separated data validation list that exceeds 255 characters (including commas) will corrupt a workbook. Tested in Excel 2007, 2010, 2013.
How To Verify That A Workbook Has Been Repaired By Excel
Users who are not trained to identify Excel's corruption warnings may repair a workbook inadvertently. Excel stores a summary forensic report in the application temp folder, which can be retrieved (if not erased by a disc cleaner) in order to verify beyond doubt that an Excel workbook has been repaired by a particular user, even if the person denies knowledge of such incident due to ignorance!
C:\Users\....\AppData\Local\Temp\errorXXXXXX_01.xml
Full path of the workbook repair report file
Contents of a workbook repair report in XML format
Add-ins For Corruption Recovery & Repair
|
Formula Auditor Add-in
Analyze formulas & repair corruption errors that may prevent files from opening in Excel |
Please contact us for a free workbook assessment & bespoke file recovery or leave a comment. Entire VBA projects can be recovered from any corrupted XLS, PPT, DOC legacy file and from Office 2007+ OpenXML files, from both locked and unviewable VBA projects.
