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

Excel repair

Good day,

To manually repair a file of MS Excel 97-2003

1. On the File menu, click Open.

2. In the Open dialog box, select the file you want to open, and click the arrow next to the Open button.

3. Click Open and Repair, and then choose which method you want to use to recover your workbook.

For MS Excel 2007 another commands

1. Click the Microsoft Office Button Button image, and then click Open.

2. In the Open dialog box, select the corrupted workbook that you want to open.

3. Click the arrow next to the Open button, and then click Open and Repair.

4. Do one of the following:

To recover as much of the workbook data as possible, click Repair.

To extract values and formulas from the workbook when an attempt to repair the workbook is not successful, click Extract Data.

Check out this article to see much more about Excel file recovery process:- http://www.techrepublic.com/blog/10-things/10-ways-to-recover-a-corrupte...

If all above methods don’t work, then you may try third-party tools such as Excel Fix Toolbox from source: http://www.excel.fixtoolbox.com/

You may use it to recover some Excel files successfully. It provides a free demo version so that you can try to see if the data you want can be recovered or not.

Good luck!

Cannot save Excel file

Hi,
When I open one specific Excel file, I got the message stating that" Excel cannot complete this task with avaulable resources. Choose less data or close other applications". Then I set the priority to high for excel.exe. But seems there is no change.

Also, I could not save the file after I edited it.
I tried to create a new excel file and copy all contents to the new file. I still cannot save the new file.

Any solution for this? Thank you.

Regards,
Janine

Recover Excel file

You can use above recommended solutions to recover many file if your excel file not recover give methods then try 3rd Party Excel Repair tool from reputed company.

Before buying any software must download demo version & check your data recover or not.

Read more tips at here : http://msofficerecovery.blogspot.com/2014/04/how-to-repair-corrupted-exc...

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.