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.

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.

Nice!

Worksheet that I was given had 60 sheets! Perfect little trick. Thanks.

Didn't work for me

Yes, nice trick, but as I only have one sheet, it doesn't help me.

Nick's picture

big excel file

if it's only one sheet, the most likely cause is formatting.

Advice is to recreate the sheet with no formatting, then add the formatting to entire sections - columns or large ranges

- Do not format a range of cells, and copy that formatting to other cells.

 - you can find out exactly how small your file could be by copying only the data to a new sheet and saving that.

Nick's picture

Cause Of Big Excel Files

Very nifty!

Nice trick

- Laks