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.