Excel compresses its files using Zip-Deflate and stores it. However, the compression level is far from the best. To reduce the size and send it across I have to unzip and re-zip it manually everytime. Is there a way to change the compression level setting in excel so that it compresses and stores the file using best method possible?
You can also through a VBA macro replace the zip engine.
A macro that uses 7Zip to do the compression can be found in the article:
Zip Activeworkbook, Folder, File or Files with 7-Zip (VBA).
Another method which usually gets about 50% improvement in size is
by saving the file in the
xlsb format that was introduced in Excel 2007.
Still another one is to
Save As the file, which sometimes reduces its size, presumably
From the post In which case should we use the xlsm or the xlsb format? :
The difference with xlsb seems to be that the components are not XML-based but are in a binary format: supposedly this is beneficial when working with large files. source
.xlsx loads 4 times longer than .xlsb and saves 2 times slower and has 1.5 times a bigger file. I tested this on a generated worksheet with 10'000 rows * 1'000 columns = 10'000'000 (10^7) cells of simple chained =…+1 formulas
There are also generic methods for reducing the size of an Excel file. See for example:
A workaround is to recompress the XLSX ZIP files.
You could use the AdvanceCOMP advzip utility for this. To use the 7z compressor run:
advzip --recompress --shrink-extra huge.xlsx
If you have the time, use the zopfli compressor:
advzip --recompress --shrink-insane huge.xlsx
You can also increase the number of iterations for the compression by using the