Every now and then, for no apparent reason, when we try and save an Excel file that has been used for a awhile, we are told that the file can’t be saved as it is corrupt and Excel will try and repair it. If you eventually are forced to repair the file and save as a new version, it is useful to know what is lost when repairing a corrupt Excel file.
With luck all the data and formulas will stay (not a guarantee re the formulas). If you don’t have a recent enough version, you may need to save the repaired file (as a new name) and then open both and (unfortunately) manually work through and redo some of the ‘lost in repair’ features.
Table of contents
Repair the Excel file
Before understanding what could be lost with a corrupt file, you need to get it to open. In the YouTube video below, it shows a way to try and get it to open in Excel.
Now that you have opened it, some ideas of what could have changed/ been removed. Note that once a file corrupts, unless you can find the exact reason and correct it, it is dangerous to continue with that file. Try rebuild it or revert to a previous version.
Repaired Excel file changes
Below a listing of the type of items that could be removed. Note that this is not comprehensive and not all of these will happen every time.
Potentially Serious ‘Repairs’
These may affect the actual workings of a spreadsheet. Formulas no longer updating, input options no longer being available etc.
- External links especially if it is part of a formula e.g. external link x 1.15 (effectively it becomes a paste as values),
- Charts/ graphs,
- External connections e.g. links to a website or PowerQuery links,
- Data Validation settings (drop down lists disappear),
- Conditional formatting (warning cells may not give you any warning any more).
Repairs worth noting
Useful to know but hopefully they don’t affect the actual workings.
- Comments/ notes,
- Column widths are reset,
- Freeze panes settings,
- Hyperlinks.
Other things to look for
Based on the above, it seems that besides what is IN a cell, anything ON a cell is susceptible to ‘repairs’. So numbers, text and most formula should stay, but anything ON the cell (data validation, conditional formatting, notes) is removed.
So think through all the little extra features that you add on. These are likely to be affected. Some are just display type issues and not as serious, but some may remove controls or stop warnings being issued.
Related
Trying to Repair Corrupt Excel Files
‘We found a problem with some content’ corruption issue
The recommended online Excel course to address these types of issues