fbpx

How to Reduce Excel File Size

Do away with significantly large Excel files in financial modeling

How to Reduce Excel File Size?

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, as listed below.

 

Summary

  • Reducing the file size helps to improve Excel’s running speed and simplifies the process of sharing the file with others.
  • While building financial models, the Excel file size can be reduced by removing unnecessary data, formula, worksheet, formatting, and Pivot Cache.
  • The Excel file size can be further compressed by saving the file in binary format or compressing it into a zip archive.
  • Despite the benefits of smaller file size, the above methods also come with some drawbacks and choosing which method to use depends on the circumstances.

 

Ways to Reduce Excel File Size

 

1. Remove unnecessary worksheets, data, and formulas

The number of worksheets and the amount of data contained in an Excel file are directly related to the size of the file. Deleting the unnecessary worksheets and data is the simplest and most efficient way to reduce the excel file size.

Converting unnecessary formulas into values also helps to deflate the file size. This can be done by copying the selected cells and pasting them as “Values” under the “Paste Options” tab. The shortcut for this step is Ctrl + Alt + V + V.

 

Reduce Excel File Size - Converting unnecessary formulas into values

 

Financial models are required to remain dynamic and adapt to changes in inputs. Thus, it is important to identify whether the number in the cell is subject to inputs or assumption changes before converting the formula into a fixed value.

 

2. Remove formatting

Although formatting such as highlights, borders, and conditional formatting improves the visualization of an Excel worksheet, it also inflates the file size.

You can use the “Clear Formats” button in the “Editing” section under the “Home” tab to clear the formats of selected cells.

 

Reduce Excel File Size - Remove formatting

 

To clear conditional formatting, you can use the “Clear Rules” button in the “Style section” under the same tab.

 

Reduce Excel File Size - Clear conditional formatting

 

3. Remove Pivot Cache

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.

One way is to delete the Pivot Cache while keeping the source data. This can be done by unchecking “Save source data with file” under the “Data” tab in “PivotTable Options.” You can check “Refresh the data when opening the file” under the same tab to allow Excel to automatically general a Pivot Cache when the file is open or refresh the pivot table manually.

 

Remove Pivot Cache

 

Another much simpler way is to delete the source data while keeping the Pivot Cache. The original data set can be regenerated by double-clicking the “Grand Total” cell in the Pivot Table.

 

4. Save in binary format (.xlsb)

Excel files are typically saved in the XML formats (.xlsx or .xlsm) without specifying. Choosing the binary format (.xlsb) while saving the file can dramatically reduce the file size.

 

Save in binary format

 

Excel Binary Workbook

 

However, there are some limitations to the binary format. The binary format does not show whether the file contains a macro, which may lead to some risks and malfunctioning. Another limitation of the binary format is that Power Query cannot read data from it.

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.

 

5. Compress the file

Compressing the document into a zip file typically reduces the file size by 10%-15%. Although this extraneous method does not speed up the Excel worksheet, it makes the sharing of the document much easier. This can be done by right-clicking the Excel file and choosing “Compressed (zipped) folder” under the “Send to” tab.

 

Compress the file

 

Compressed (zipped) folder

 

More Resources

CFI offers the (upcoming) Business Intelligence & Data Analyst (BIDA)® certification program for those looking to take their careers to the next level. To keep learning and developing your knowledge base, please explore the additional relevant resources below:

  • Basic Excel Formulas
  • Documenting Excel Models Best Practices
  • Developer Tab
  • Financial Modeling Best Practices

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now

to take your career to the next level and move up the ladder!