Delicious bits of Excel and Access Training from DataPig Technologies

How to Compress xlsx Files to the Smallest Possible Size

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.

43 thoughts on “How to Compress xlsx Files to the Smallest Possible Size”

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.

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.

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.

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

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.

“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 🙂

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.

Very interesting. It works and reduced the size by 25%. However when I opened and saved the file all reduction is reverted and file is again the previous size. This means we need to do this everytime when sending files may be…

I discovered this on my own as well and decided to write a utility to do the process via a right click command in windows (or via command line)

I placed the full Visual Studio 2010 .NET project in a zip file located below. (there is a working “XLSXRezip.EXE” version in the “Debug\Bin” sub folder but I put entire project so you can recompile yourself,modify or for trust purposes). You may need to re-register the Ionic.zip.dll library in the output folder.

Basically put the XLSXRezip.exe somewhere you plan to keep it then run it. It will wait for user input.
Enter “REGISTER” and press return. It should add a right click option in windows so you can right click any file and perform the REZIP operation.

Original files are renamed to append “.orig.xlsx” to the end of the file so they are never destroyed.

I’m intrigued when reading through the thread. Though, I do have a different type of question in this area. I have six locations which I made a reporting workbook with four different worksheets that are exactly the same. When I look at each file their size is relatively the same size of 105KB but I’m wondering how one of the six has a file size of 2824KB when the worksheets are identical and protected as xlsx using office 2010.

I followed the suggestion to save the file as xls which reduced the size from 24M to 6M. When I then saved the smaller xls file as an xlsx format, the size was further reduced to 1M.
This process, also rationalized the scroll bars to be appropriate to the new size.