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

Macro buttons!

Great tip, I was able to narrow it down to the offending worksheet. Come to find out if you copy a worksheet with a macro button and paste over another worksheet it will not replace the target sheet macro button, rather add another one right on top. There was over 6K layered macro buttons!

Identify the sheet

I find my problem sheets by moving the right scroll bar slightly. Normally, when you move the scoll bar, it will track with your data. If the movement takes me quickly past the bottom of my data (usually to the bottom row in both 2003 and 2010), I have found my offending sheet. I found a user that had approximately 250+ files with 40+ sheets. Each workbook was around 95M. By deleteing the unused rows on all sheets within the workbook, the workbook was reduced to 1M.

Alternative Idea - Delete Selected Sheets.

In my case, trying the above was taking an hour to create an html file (the offending sheet was very big).

An alternative is to delete selected sheets to find the big one. Eg. Make multiple copies of your workbook. Delete all sheets except one in each copy(leave a different sheet in each one). You can now see how big each sheet is.

Deleting selected sheets

It would be more efficient to delete half of the sheets and check the size, then half again or the other half if the offender wasn\'t in the first group.

Thanks Nic

Thanks Nic, it was the formatting in my case,

cleared all the formatting and from 37 MB came down to 3.15 MB...

Thanks Nick! 9693KB's to

Thanks Nick! 9693KB's to 663KB's... you just saved me weeks of my life!

Big excel file

m not understanding the trick....after saving.....where to go?..is into internet explorer or to C/: drive and what next to do........
Please help me..m facing big problem in processing my data with 70MB file....
Thanks in advance...
mail: veenu_yadav2003@rediffmail.com

Nick's picture

go to whereever you saved it

go to whereever you saved it

Worked great for me

I identified the offending Sheet, copied the data to a new sheet, deleted the offending sheet, then formated the replacement sheet exactly the same as before.And now the workbook is the expected reasonable size.

Leo in St. Louis

Huh?

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