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.
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.
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
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.
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.
Rich: I have found that saving an xlsx to xls will often allow the file to be zipped down to a much smaller size.
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.
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.
It's not the size of the file, it's what you do with it!
(sorry, guys, I just couldn't resist… LOL!)
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.
Lynda: You wicked monkey, you.
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.
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.
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!!!!
Excellent help regarding .xlsb. Thanks a lottt.
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….
Thanks
Works reasonably well as an intermin solutions. Recieved a rough 35 percent file size reduction.
Is there an Excel macro that can do this? I have Excel 2010 and would like to automate this process.
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
The method above corrupted my Excel file. The .xlsb option reduced the size from 1.88Mb to 792kb.
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