How to reduce Excel file size

Sometimes the models we build in Excel can be large and complex. Much of this will be necessary and outside your control. There, however, are a few causes of unnecessary file size bloat that you can and should manage.

1 Larger than necessary used range

Excel keeps track of what it thinks is the range of active cells in the model. This is called the "used range". If a cell once had data in it, but that data was deleted, Excel will still consider it a used cell.

Moving code around, formatting sheets, and changing timelines can cause the active range that Excel believes we are using to be much bigger than the range we are using. Excel has to keep track of all these cells, even if they are empty. This can cause significant file size bloat.
Ensuring the used range is not larger than it needs to be will help keep Excel file size under control.

2 Cross worksheet functions

In controlled testing, cross work sheet functions add around 20-25% more file size than functions that work on a single sheet.  You can keep file size down by setting up your model so that functions do not need to read across worksheets to obtain their ingredients.  

3 File format

Saving your file as a binary file type (xlsb) will help reduce the file size (usually by about 25% compared to an xlsx file type.

See this section to understand more about Excel file types.

In the next section we'll look at how to manage the used range in your model.

Comments

Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.

Subscribe to Financial Modelling Handbook

Don’t miss out on the latest financial modelling guides. Sign up now to get access to the library of members-only guides.
jamie@example.com
Subscribe