78. Excel Tips - Why Is My Excel File So Big ?

Nick's picture



Excel files seem to just get bigger and bigger often balooning in size at the blink of an eye, but how do you find out where all the extra file size is coming from ?

  • There is a way to separate out each object in your file, and examine it's contribution to the overall file size

Here's a screen shot of our innocent-looking Excel file:

why-is-my-excel-file-so-big-?

..and here is where all the file size is coming from:

why-is-my-excel-file-so-big-?

- in this case, the culprit is clearly Sheet2, so that's where to start looking..

 

Here's how to do it:

  1. Save your file
  2. SaveAs a completely new name so that you can mess about with it
  3. SaveAs Web page (choose 'Other Formats' in Excel 2007)
  4. Open Explorer
  5. Navigate to where you saved the file
  6. Open the folder with the same name as your file
  7. Sort descending by the file size
    • The largest ojects will be named, and at the top

Download sheet to practise how to find out: Why Is My Excel File So Big ?

 

Training Video on: Why Is My Excel File So Big ?

 

What next ?

Check out this tip on how to fix a corrupt Spreadsheet... Following these steps will also reduce your file size.

 Also check this out:

95. Excel Tips - Cause Of Big Excel Files

Also, if you have a recent version of Excel, you can try saving as an XLSB - this is sort of like Excel's equivalent of a zip file.

AttachmentSize
why-is-my-excel-file-so-big.xls40.5 KB

Huh?

This doesnt work. What am I doing wrong? I am following the instructions.

Nick's picture

what Excel version ?

what Excel version ?

Down from 140MB to 700kB

Wow, thanks, great tip.

Martijn (NL)

This was fixed for me by

This was fixed for me by first copying the workbook into a new Excel file, then changing my "Save As Type" from "Excel 97-2003 Workbook" to just "Excel Spreadsheet".

VERY HELPFUL!!!

Thanks - - your trick worked - i was able to narrow down the specific problem-sheet and then i just cut/pasted the data into a new sheet, replaced, and resolved the issue - - my file had suddenly gone from a 1MG to a 25MG. Now it's back down to 1. THANKS for sharing your knowledge!!!!

THANKS

The trick of splitting up works great. It clearly showed that my 7th sheet was the big one. I then selected all empty rows below my data until the end (from row 39 to 1048576) and did "Clear - All". After saving to disk, the file had shrunk from 23MB to 184KB. And I get the feeling that it's working a lot faster again.
Regards, Eric (NL)

Clear All worked for me

I selected all empty rows below my data using and did the "Clear - All" - 13Mb to 121kb. Well pleased.

Big Al - The peoples pal

Sheet Names

I tried this and see that sheet0003 is my culprit but I don't know which of my three sheets is sheet0003 they've all been renamed and moved around. How can I know which is sheet0003?

Nick's picture

sheet 3

erm... it's the 3rd sheet.

; - >

Find the sheet

It may not be the third sheet physical if you rename and move the sheets around, the sheet number is the original tab number. But open the new large document and you can look at the data. Then find that sheet in the original excel.