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
| Attachment | Size |
|---|---|
| why-is-my-excel-file-so-big.xls | 40.5 KB |
»
- Nick's blog
- Add new comment
- 51835 reads
Very nifty!
Nice trick
- Laks
Didn't work for me
Yes, nice trick, but as I only have one sheet, it doesn't help me.
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.
Cause Of Big Excel Files
Also related to this:
95. Excel Tips - Cause Of Big Excel Files
Nice!
Worksheet that I was given had 60 sheets! Perfect little trick. Thanks.
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?
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.
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
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".
Down from 140MB to 700kB
Wow, thanks, great tip.
Martijn (NL)
Huh?
This doesnt work. What am I doing wrong? I am following the instructions.
what Excel version ?
what Excel version ?
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
Thanks Nick! 9693KB's to
Thanks Nick! 9693KB's to 663KB's... you just saved me weeks of my life!
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...
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.
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.