8

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?

  • Do you use any links to other files from where you import data? – lowak Apr 9 '14 at 13:18
  • With the solution below, I found a 390MB uncompressed worksheet with only 130 rows, 8 columns, no format besides freezed panes, and having ONLY VLOOKUPs to other sheets of the workbook. I don't get how it got to be so, so heavy. – s_a Apr 9 '14 at 13:46
  • It has no objects neither. I don't get this at all! – s_a Apr 9 '14 at 13:50
  • I will put it this way: my data when used linking to another workbook weighted 2x more than raw data. – lowak Apr 9 '14 at 13:56
  • That's because Excel stores both the values and the formulas (vs just the values in the original). // I found out the instructions below had an error; with the corrected instructions I managed to find and fix the error. – s_a Apr 9 '14 at 14:23
17

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: Excel workbook structure example

  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: Excel workbook structure example

  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.

Excel workbook structure example

  • 1
    Fun! You learn something from me, and I learn something from you! :) – LPChip Apr 9 '14 at 13:55
  • 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! – s_a Apr 9 '14 at 14:22
4

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

Helpfully Annotated Screenshot of instructions from provided link

  • 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. – robinCTS Jun 13 '18 at 3:00
  • Good point RobinCTS. – Andy.Collette Jun 14 '18 at 16:21
  • 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... – Andy.Collette Jun 14 '18 at 16:39
  • 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. – robinCTS Jun 14 '18 at 16:52
  • Awesome, thanks! Is that something I could have done myself? – Andy.Collette Jun 14 '18 at 20:49
1

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.

  • 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). – s_a Apr 9 '14 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.