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

Question

I have an Excel .xlsx that weights 47MB. I'd like to know which parts of the workbook are the heaviest and work them, since I haven't made much progress manually trying to identify and reduce the filesize. Is there any way of knowing findout out how much each sheet weights in an Excel workbook?

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.

Comments

[s_a]

Thanks for answering. It is very a very uncommon problem. The main problem involves neither images nor the last row bug (although thanks for pointing it out, a couple of the MB were actually due to that issue in another problematic sheet and I didn't know that it could increase file sizes, I just thought that it messed with the scrollbar).

[LPChip]

Fun! You learn something from me, and I learn something from you! :)

[s_a]

Thanks, but notice the method was lacking an important step and lead to an error. Now it works correctly and I got to id the worksheet!

[robinCTS]

This would probably be a reasonable enough answer worth keeping if you actually edited it to include the salient points from the link you supplied. Links can die, and then the answer becomes useless.

[Andy.Collette]

Good point RobinCTS.

[Andy.Collette]

Hopefully the linked image doesn't become broken... if this is an issue I can host myself, but thinking i.stack.imgur should be pretty stable...

[robinCTS]

Stack Exchange (the network Super User is a part of) have formed a partnership with Imgur, so if Imgur dies, all of the network will break! Should be rock solid. I've in-lined the image for you.

[Andy.Collette]

Awesome, thanks! Is that something I could have done myself?

[robinCTS]

As far as I know, you need 10+ reputation to do so.

[s_a]

Great answer! I didn't know about this at all. And thank you for the compliment!