How can I find out which parts of an Excel workbook are the biggest in size?

Solution 1:

Didn't mean to answer my own question but I found out how to do it afterwards. So here's the deal:

1. First, make a copy of the file, rename it as .zip, open it and navigate to [zipfile]\xl\worksheets\ There you'll see the .xml files with their uncompressed size. For instance:

2. Identify the biggest worksheet, in this case sheet6.xml

3. Open the file [zipfile]\xl\_rels\workbook.xml.rels and find out the corresponding relationship id of the previously identified worksheet.xml. In this case the r:id of sheet6.xml is 10:

4. Open the file [zipfile]\xl\workbook.xml. Using the rid of the big .xml file you got above and the structure of workbook.xml (shown below), find out the name of the big sheet in your workbook.

Solution 2:

I am unable to Comment (not enough points) but I used this in addition to the Awesome answer provided by S_A to get my bloated file from 75mb down to 1.7mb...

I had NEVER heard of the "Clean Excess Cell Formatting" feature before... (BTW, it's part of Excel, not a 3rd-party product...)

Sorry for submitting this as an answer, but I wanted to tell you about this helpful tool, and thank you for your helpful discussion! Hoping to get enough points someday to participate...

Edit: at the recommendation of robinCTS, I'm going to quickly show how to enable/access this tool from within Excel

Solution 3:

A 47 NB xlsx file is very uncommon unless it has lots of images in it. If yours hasn't, the chance is big that you are having one of those exported files that gone wrong.

Basically what happens is that when the xlsx file is exported, opened with excel and then saved again, it saves the file with lots of empty rows and columns. You can identify this by seeing huge scrollbars (eg: the scrollknob is tiny).

To counteract this, make a selection from the first empty column all the way too the last (by using the header cells, not selecting individual cells), then on the header, right click and select delete. Do the same for the rows (which are a lot!!!) and then save the file.

Excell should now save the file and if this was the problem, it'll go from 47 mb to maybe 2 mb.