Reduce Excel File Size
Margarete Klauer
The Excel file size of a financial model might become considerably large due to an increasing level of complexity and amount of details added. The workbooks suffering from large file sizes would run much slower.
Circulating such large files with your colleagues would also become a painful process, especially when they are over 50 MB in size. Thus, it is always important to minimize the file size while building your financial models. There are several methods to help you reduce the Excel file size[1], as listed below.
The number of worksheets and the amount of data contained in an Excel file are directly related to the size of the file. Deleting unnecessary worksheets and data is the simplest and most efficient way to reduce the excel file size.
Excel will automatically create a Pivot Cache that contains a replica of the original data source when a Pivot Table is inserted. Any changes made on the Pivot Table are not directly connected to the source data but the Pivot Cache instead. Removing the duplicated data before saving helps to deflate the file size.
Additionally, different from the XML format, the binary format is not open-source readable. Thus, the XML format is more widely used as it better adapts to other third-party tools. Therefore, saving in binary format is usually not the first choice when the file size is not too large.
In this article and others that will follow, we add to the first article and move beyond the size of a worksheet to features of Excel that, through either file longevity, external links, or compatibility issues, can cause a file to increase in size and decrease in speed.
Typically, Named Ranges will not significantly increase file size. Sometimes, however, they get corrupted in some fashion and add significantly to the file size. We recently received a workbook which contained over 2,600 Named Ranges, many of which were hidden and included external links. By removing the 2,500+ that were not used, the file size was successfully decreased from 8MB to 1MB.
How to reduce the file size really depends on what you're writing to that file. Could you share an example of your workflow and/or the output file? In the meantime, here's a couple of initial thoughts: