The larger a spreadsheet gets, the more its performance is comprised. Large spreadsheets start to experience stability issues, calculations slow down and sharing them becomes inconvenient for both the sender and recipient.
The first step in reducing the size of a spreadsheet is to ascertain which sheets are causing the greatest “weight” in the model. To do this, simply:
- Open the File Explorer where the Excel model is saved
- Display file name extensions in the “View” tab
- Change your Excel file copy’s extension to “.zip”
- Then open the zipped folder and click on xl > worksheets
- Sort, and then view by size
You will quickly be able to spot the “heaviest” sheets. Focus on those first and consider reducing their size by:
- Simplifying formulae. Long, complex formulae not only add to the risk of error, but they materially contribute to performance and model weight.
- Check for unused conditional formatting.
- Delete noise – images and logos typically add little value.
- Limit each worksheet to its used range.
- Avoid duplication. You are unlikely to need data or workings twice. Duplications add weight and more risk of error.
- Remove unnecessary worksheets.
- Keep data formatting to a minimum. Font colour, type, and weight.
- Remove unused data. We often import data as a temporary measure and then forget about it. If it is not adding value, delete it.
- Delete watches when you no longer need them. They might be useful when you are building a model, but they also quickly add weight.
Finally, a great way to reduce the size of an Excel file is to save it in Binary Format (.xlsb) rather than .xlsx.
Stonefield Advisory is constantly working with clients to build models that are functional and of a high quality. If you can keep the weight of models down, you will go far in delivering an improved user experience.
Tim Loughton and Andrew Lees are Directors of Stonefield Advisory, a data modelling and visualisation consultancy. Stonefield has a presence in both London and Cape Town.