Back in January I posted a few ideas to reduce workbook size. This post got quite a few comments, so I’d thought I’d write them up into a proper post.

Also Gorden has noticed a strange thing happening, so I wanted to highlight that a bit better too.

Here is a check list!

• Zip the file!
• Used Range see here.
• Removing any VBA code
• Remove Formatting.
• Use Rob Bovey’s code clearer
• Duplicate pivotcaches (ethoros)
• Excel Filter Ranges (Gordon)
• Embedded Pictures  (Jon von der Heyden)
• XL 2007, Store as .xlsb  (sam)
• VlookUps, External data (Charlie Hall)

Please check back in the original post for explanations of these if they don’t make sense.

And here the one Gordon recently found, which is the odd one! What do you make of it?

I recently found a method that can reduce the file size of an Excel file containing significant amounts of VBA, sometimes by as much as 50%.

1. Note file size
2. Open file but do not enable VBA/macros
3. Make no changes
4. Save & close file
5. Check file size again

Even after using Rob Bovey’s Code Cleaner this can sometimes make a huge difference to the size of the file. Many of the workbooks I manage are read-only and reside on a network share somewhere, and as this can make these files much smaller (1300KB down to 700KB etc.) it improves load times, particularly over VPN connections, resulting in an improved user experience.

The files work exactly the same after this is carried out and although I’ve done some examination to see what might have changed I’ve not managed to find anything. Most likely some internal decrofting* is responsible, but I’ve really no idea why this works.

I’ve just tried this on a file and it went form 7.2 megs to 6.6 megs, not bad for doing nothing.

I cant think what it would be that causing this, maybe P-code compilation or something?

* decrofting, some sort of Scottish thing. Crofting is small scale land sharing for framing, so decrofting must be the un sharing of land… or something!

As you work and work on a spreadsheet it has a tendency to grow in size. This is caused by adding more data, formulas and charts etc, but as file size grows workbooks can become unstable. I’m not sure what the current thinking is, but workbooks over 100mb always used to worry me. I know some folks will have bigger ones that are stable, but it’s just a rough guide.

Anyway it turns out that it’s not only the data, formulas and charts that effect work book size, but other, possibly less obvious things too.

Luckily there are some steps we can take to help reduce workbook size, making them more stable and reducing the file size on disk – think e-mail.

The effect will differ massively depending on the workbook you start with, but for illustration, this shows what happen to a workbook after applying each method in isolation.

What are the options?

• Zip the file – and remember if you’re trying to get the file under a certain size, maybe to get it through a corporate firewall, other zip applications are about, which can give smaller file sizes than the built in windows one.
• Used Range – Clearing up the used range means that empty cell don’t get sotred in the file, Excel is not great at clearing up it’s owe file structure, good news is its easy to fix, see here.
• Removing any VBA code from the file can help reduce the file size – it’s not always possible, but if it is, maybe you could try it! Also watch out of userform with massive graphic files embed in them – classic!
• Remove Formatting. Having lots of complex formatting is a key driver for file size, removing unnecessary or overly complex formatting can drastically reduce the size of you files.
• Rob Bovey’s code clearer, strips out VBA models can re imports them, this can help clean them up – if you been working on a bit VBA project, this can help – big time!

So there you go, a few ideas about how to reduce file size, maybe these will help someone.