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

my excel clean but data still messy

hi Nick. my excel file corrupted in such different condition.. when i open the file, it says the file is corrupt,and ask me whether i want to open it anyway or not. so i click "yes", and it says "file not loaded completely". then i click "OK", the file open, and my spreadsheet got weird symbols came out for almost thousands of line (my data messed up i think).. then i tried ur method, of course the corrupt dialog doesn't shows up anymore, but my data still messed up, altered with weird symbols still there.. please help me.. i got very important data in it..

File size still too big and very slow

I tried the method you suggested, however the file size remains the same and it is very slow when I try to use copy/paste, delete columns, go from one to another sheet.

The file size is 1.57 MB.

Please suggest.

Thanks,
Sam

Nick's picture

sam - try turning calculation

sam - try turning calculation to manual

Creating a copy

Hi Nick,

I noticed that you draged the sheets to the new workbook. so they disappeared from the odd file.
Could I create a copy of the sheets instead of draging them ?(they will remain in the odd file and be transferred to the new one). will the file be uncorrupted if i do that?

Thanks

tjeffryes's picture

Copying works differently than moving

Please be advised that if you have cells containing more than 255 characters and you copy, you'll get a warning saying that data will be truncated. If you move the sheets, you don't get this warning. This is at least for Excel 2003

Creating a copy

You can press ctrl while dragging to create a copy

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.