Author: Sarthak Bhalerao
Table of Contents
- Why is reducing file size necessary?
- Different ways to reduce Excel file size
Why is reducing file size necessary?
The Excel file size might be considerably large due to an increased level of complexity and the number of details added. The worksheets that have large sizes will run much slower as compared to others. Circulating such large files with your colleagues would also be a painful process, especially when they are over 50MB in size. These cannot be sent via email as it exceeds the limit. Thus, while making such excel files, it is very important to minimize the file size.
Different ways to reduce Excel file size
There are several methods in which you can reduce the Excel file size, as listed below:
- Remove unnecessary worksheets, dada, and formulas
The number of worksheets and the amount of data in them is directly related to the file size. Deleting the unnecessary sheets and data in them is the most efficient way to reduce the file size. Converting redundant formulas into values also helps to decrease file size. This can be done by copying the selected cells and pasting them as “Values” under the “Paste Options” tab. The shortcut to this is Ctrl + Alt + V + V.
- Remove Formatting
Formatting tools like borders, highlighters and other tools improve visualization, they also inflate the size of the excel sheet. You can clear all the formatting under the ‘Editing’ section under the ‘Home’ Tab.
- Remove Pivot cache
Excel stores the pivot cache whenever the user creates a Pivot table. All changes made to the pivot table are connected to the pivot cache. One way is to delete the Pivot Cache while keeping the source data 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.
- Save the worksheet in binary format(.xlsb)
Excel sheets are saved in XML formats without specifying. Choosing the binary format while saving will reduce the size drastically.
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.
- Compress the file
Compressing the document into a zip file reduces the file size by almost 10%-15%. This method does not speed up the worksheet but sharing becomes much easier and faster. To compress a file, right-click on the excel file and under the ‘Send to’ option, select ‘Compressed (zipped) folder’.