How to Compress xlsx Files to the Smallest Possible Size

August 18th, 2011 by datapig Leave a reply »
A colleague of mine (Tim) is both the smartest and cheapest guy I've met in a long time. Tim has a knack for analyzing the cost savings of things. So much so that it borders on amusing. For example, he buys his coffee creamer in bulk on-line because it's "the best deal". Bulk coffee creamer! Man…that's commitment.
.
.
His knack for saving has apparently spilled over into disk space.
Tim showed me a trick he uses to compress Excel xlsx files to make them as small as possible. Today, I'll share his trick with you (my 12 fans).
.
.
I've got this xlsx file that's 20 MB big. I need to shrink the file down to a more acceptable size.
Normally, I would convert this file to an xls file which would make it much smaller. But this particular file has too many rows to convert to an xls.

.
.
So the first thing I try is to zip the file as is. I simply use WinZip on the file.
As you can see, it compresses down to (17 MB) but it's really not that much smaller. This is because xlsx files are technically compressed files themselves. And we all know that when you try to zip a zipped file, you don't get all that much shrinkage.
.
.
Here's the better way:
I take my original file and change the extension to .zip.
.
.
I then extract the contents of the zip file.
.
.
Once the contents are extracted, I zip them back up using a compression program (like WinZip).
.
.
This leaves me with a zipped file containing all my contents.
And you'll notice that the size has been compressed down to 14 MB.
At this point, I can change the extension back to .xlsx.
.
.
Once the file is changed back to xlsx, it works just like a normal Excel file – only it's 6 MB smaller than the original.
.
.
So you may be wondering what's the deal here. Well, apparently the compression technology that Excel uses to create xlsx files is … how should I put it … piss-poor.
.
By extracting out the source files and using your own compression tool, you can compress your xlsx files to a much smaller size.
.
Thanks Tim – you cheap bastard.
Advertisement

31 comments

  1. Khushnood Viccaji says:

    Granted that Excel’s compression algorithm is piss-poor, but I use a much less convoluted method :)

    Starting with Excel 2007 onwards, you can save any workbook in the XLSB (Excel Binary Workbook) format. That can give nearly 50% saving in file-size as compared with xlsx files — sometimes even more, depending on the contents of the workbook.

    The only, ‘drawback’ of this method is that you cannot view its contents like any of the other XML-based file formats (xlsx or xlsm).

    I have saved this (xlsb) as the default save-as file format in my Excel settings.

  2. Oakhome says:

    I’m fan # 13. Not sure if I’m lucky or unlucky in that regard, but appreciate your site none the less.

    Surprised as I was under the impression that *.xlsx file sizes were smaller than *.xls

  3. Omar says:

    I also use the .xlsb format. I haven’t compared file sizes to .xlsx, but files I converted from .xls reduced in size as much as 90% in some cases. A 50% reduction is routine.

    The biggest benefit I get is that opening a file across the network takes much less time now.

    The reason I avoid the .xlsx format is I don’t want to be changing file formats every time I decide to drop a macro in a file.

  4. Rich says:

    when I tried this on an xls file i found that proportionally I got

    xls = 100%
    winzip xlsx = 31% of original
    xlsb = 32% of original
    xlsx = 36% of original

    Can you explain the comment in the above ” I would convert this file to an xls file which would make it much smaller” – usually I find that xlsx’s are smaller.

  5. datapig says:

    Rich: I have found that saving an xlsx to xls will often allow the file to be zipped down to a much smaller size.

  6. JP says:

    How about in-file techniques like reducing the used range, removing whole-column formatting and formulas? I know that isn’t what you mean but that might reduce the file size further.

  7. Piss-poor from a file size point of view, but what about execution. Does an Excel compressed file open faster than a winzip compressed file? It’s possible that MS traded some file size for performance.

  8. Lynda says:

    It’s not the size of the file, it’s what you do with it!

    (sorry, guys, I just couldn’t resist… LOL!)

  9. datapig says:

    DK: Good question.

    Both the original 20 MB file and my newly zipped 14 MB file take about 13 seconds to open. If there is a performance difference, I can’t see it.

    What’s interesting is that if I open my newly zipped 14 MB file, and “Save As” a new file name, Excel will not keep the 14MB size. The file that is saved will be 20 MB.

  10. datapig says:

    Lynda: You wicked monkey, you.

  11. Ola says:

    Very interesting post
    I just tested with an ordinary workfile (2sheets with 18.000 rows + around 10 pivottables + charts)

    xlsx: 14,8Mb original (company e-mail limit 10Mb)
    xls: 58Mb (save xlsx as xls)
    xlsx: 8,9Mb (change xlsx to zip, reziped change to xlsx. It might be that over time the file gets fragmented.)
    xlsb 11,2 (save xls as xlsb)
    xlsb:7,2Mb (save original as xlsb and save reziped as xlsb exactly –> same file size)

    Advantages
    That is <50% original xlsx !!
    xlsb saves and opens far quicker.
    So I just decided never use xlsb ever again.

  12. Ed Ferrero says:

    In my experience, very large spreadsheets are usually large because they contain a lot of data.

    That being the case, the best way to reduce the size of the spreadsheet is to get the data out of there. Put it in a database, or even a csv file. Then use a pivot table, or a linked table to get the data you need into Excel and do your calcs on that.

    Call me old fashioned, but I just don’t like spreadsheets much bigger than 2Mb. Yes, I do use them, but I don’t like them.

    So, separate the data layer from the calculation layer, and the reporting layer. Excel will like you.

  13. Marcela Kholova says:

    Unbelievable! Was in need of reducing 55MB file to under 50MB and didn’t find anything helpful. But with your process, the file’s now at 40MB! Perfect!!! Thanks a lot!!!!

  14. RUCHIKA VOHRA says:

    Excellent help regarding .xlsb. Thanks a lottt.

  15. I found the best solution is to :
    # Copy Range of cells you work on inside sheet

    # Press Shift+F11 (to insert new sheet)

    # Paste previous Range you copied

    # You Can Use Format Brush to format it

    # Delete Original Sheet and save

    # You will get size very smaller….

  16. NK says:

    Thanks

    Works reasonably well as an intermin solutions. Recieved a rough 35 percent file size reduction.

  17. rob says:

    Is there an Excel macro that can do this? I have Excel 2010 and would like to automate this process.

  18. Petros says:

    Zipping OpenXML files manually seems a bit risky. Please do not forget that corruption can strike legit Excel files, imagine what it can do to ‘over-clocked’, sorry, ‘over-zipped’ files.

    http://www.spreadsheet1.com/saveas-binary-workbook.html

  19. DrMaestro says:

    The method above corrupted my Excel file. The .xlsb option reduced the size from 1.88Mb to 792kb.

  20. Doug Collins says:

    Hello – Corporate E-Mail limt 3mb (I know tiny)

    Here is what I found

    Original xlsx 8,218 kb
    Tims Process reduces file to 5,849 kb
    xlsb (On file reduced by Tim’s methond) 3,162 kb
    xlsb file contains all original information

  21. Michael says:

    I would kill for a 2Mb file! At my company I am OFTEN working with 60-200Mb excel files and sometimes I get single data sets spread out over 5-6 (not quite) 2Gb Access databases!

    Just FYI, images etc laden Excel files do not have the same results as mentioned above.

  22. becks256 says:

    I work with excel files that often contain a lot of images, and subsequently our file sizes can get out of hand — up to 250MB. I thought I’d share my success with reducing the file size using the xlsx>zip method.

    Change your xlsx extension to zip and unzip it. All of the images are extracted to a folder in the group. In the folder ‘xl’ is a folder called ‘media’. All of the images are located in this folder. So I did a batch in Photoshop to compress all the images in the folder down to 72 dpi and converted them to RGB (for some reason the author left them in CMYK). Zip it back up and change the extension to xlsx.

    The ending result was that I was able to shrink a 141 MB file down to 6 MB.

    FYI: I couldn’t get the file size to reduce using built-in Excel compression features. I wasn’t going to waste my time doing copy paste special for 200+ images, and the “Compress Pictures” tool didn’t do a thing. If anyone has an answer to that I’d love to hear it, but I’m beginning to think it doesn’t work with CMYK images

  23. Prabhu says:

    if you need to further shrink the file change it to .xlsb this will reduce the file size more that what you have mentioned above

  24. Josh McCullough says:

    Please note that Excel, likely, does not use maximum compression when saving the XLSX format in order to save faster. For large worksheets, using maximum compression levels could really slow down the save time. So I wouldn’t say that Excel’s compression algorithm is “piss poor”, I’m sure they are going for performance over file size.

  25. Idz21 says:

    Khushnood Viccaji, can you comment on what you mean by this?

    “The only, ‘drawback’ of this method is that you cannot view its contents like any of the other XML-based file formats (xlsx or xlsm).”

    What is the “content” that you’re referring to? I used your method of saving an .xlsx as an .xlsb which reduced the size of my 40 MB file to 16 MB. This was the same exact savings that I got by using the original method in this post by changing to zip -> unzip -> re-zip. When I open the .xlsb file it looks & seems to function exactly like my original .xlsx which is why I wanted to get more details on which contents you noted would not work in the binary format.

    Now I just hope you will read my response to your 3 year old comment :)

  26. Doug says:

    Good info – and just fyi, if you save as PDF, it really shrinks. Yes, it’s no longer an Excel file but if you’re doing what I do a lot, showing prices, photos, etc, but the recipient does not need to use the XLS features, it’s great. For example I took a 28mb .xlsx file that has about 75 images in it, down to a 540kb PDF. And as you know both Word and Excel have built in ‘save as PDF’ functionality.

  27. Jeremy O says:

    This fix worked wonders for even my .xlsb file. Took it from 13.3mb to 13mb which was just enough to squeeze through the file size filter. Cheers!

  28. Raja Kishore Nayak says:

    How many percentage will reduce the original file size of zip folder?

  29. Richy says:

    Step 1. Save As from xlsx to xlsb
    Step 2. Rename extension to zip, extract, create zip file, rename extension back to xlsb

    Worked like a champ! Thanks!!

Leave a Reply