78. Excel Tips - Why Is My Excel File So Big ?
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:
..and here is where all the file size is coming from:
- in this case, the culprit is clearly Sheet2, so that's where to start looking..
Here's how to do it:
- Save your file
- SaveAs a completely new name so that you can mess about with it
- SaveAs Web page (choose 'Other Formats' in Excel 2007)
- Open Explorer
- Navigate to where you saved the file
- Open the folder with the same name as your file
- 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.
| Attachment | Size |
|---|---|
| why-is-my-excel-file-so-big.xls | 40.5 KB |
- Nick's blog
- Login or register to post comments
- 513424 reads
What am I missing
I've done this and taken a peek at the results. The offending sheet bloated from 8 MB to 20MB. When I look at the contents of the web page folder, it's 1.15GB. Is the size of these files supposed to be related?
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
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