How To Reduce Excel File Size

Wondering how to increase or decrease a size of file? Learn simple ways to reduce excel file size in this article.

While using Excel, the file size might become considerably large because of an increasing level of complexity and the number of details added. Heavy files run much slower.

It would also become a problem circulating such large files with your colleagues, especially when they are over 50 MB in size. Therefore it is important for us to learn how to reduce excel file size. There are many ways to do this, so let us know about them.

Ways to Reduce Excel File Size

1. Remove unnecessary worksheets, data, and formulas

The size of the file is directly related to the number of worksheets and the amount of data contained in Excel. The simplest way to reduce size is to delete unnecessary worksheets and data.

Another way that helps in deflating the file size is converting unnecessary formulas into values also helps to deflate the file size. You can do this 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.

2. Remove formatting

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

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

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

3. Remove Pivot Cache

Excel automatically creates a Pivot Cache that consists of a replica of the original data source when a Pivot Table is inserted. Changes made on the Pivot Table are not directly connected to the source data but to the Pivot Cache instead. You can remove the duplicated data before saving it to deflate the file size.

One way is to delete the Pivot Cache while keeping the source data. To do this, uncheck “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.

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)

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

However, there are some limitations to this as it 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.

Also Read
How To Remove Formula In Excel
How To Merge Cells In Excel

5. Compress the file

To make the sharing easy and quick, you can compress the document into a zip file typically reduces the file size by 10%-15%. This can be done by right-clicking the Excel file and choosing the “Compressed (zipped) folder” under the “Send to” tab.