73. Excel Tips - Fix Corrupt Spreadsheet

Nick's picture



Today's tip is one of the most useful tips I will ever write.

  • My spreadsheet is behaving oddly, what can I do ?

Most likely, your spreadsheet is corrupt, and needs to be cleaned. Corrupt spreadsheets are very common, and happen over the course of time through significant changes and file saves. Excel gets it's knickers in a twist after a while, and needs to go back to square 1. As standard practise if I am developing a large spreadsheet system, I will clean them on a regular basis. Cleaning a spreadsheet is essentially creating a new one, and copying everything across.

Here are the steps that you need to follow to uncorrupt your spreadsheet:

  1. Take a copy of your spreadsheet in case you mess up.
    1. Save file
    2. File (or office button) => SaveAs 
    3. Give it a new name.
  2. Insert a new worksheet in the corrupt workbook.
  3. Select all the worksheets apart from the one you just added.
  4. Create a new workbook.
  5. Unmaximise the workbook windows.
    • fix-corrupt-spreadsheet
  6. Drag all the worksheets across to the new workbook.
  7. Drag all the code modules across to the new workbook.
  8. Copy all the sheet code across to the new workbook.
  9. Add any references the old workbook had to the new workbook.
  10. Close the old workbook, and DON'T SAVE THE CHANGES.
  11. Save the new workbook with a new name
  12. Compare to the old one to make sure everything still works.

 

Download sheet to practise how to Fix Corrupt Spreadsheet in Excel

Training Video on how to Fix Corrupt Spreadsheet in Excel:

AttachmentSize
fix-corrupt-spreadsheet.xls38 KB
Nick's picture

Copy Sheets

Move them

- keep in mind that when you close the corrupt file, you don't save it, so any operations done do not affect the corrupt file.

 

Copy works too

I just ran into the same problem, copying the worksheets works like a charm as well.