Reduce Excel File Size

How to Reduce Excel File Size?

Below are the 4 methods to reduce the file size of the excel workbook.

Save File in Binary Format
Convert Unnecessary Formulas to Values
Compress Image
Deleting Pivot Cache

Let us discuss each of the methods in detail –

#1 – Save File in Binary Format

The excel file size could be reduced by converting the regular .xlsx format to .xlsb which is the binary format. Thereby reducing the file size to the least possible size.

Step 1 – Open excel file .xlsx format.
Step 2 – Then click on the File tab. In the File Tab drop-down list in excel choose Save As in excel.

Step 3 – Now select Browse Option, then a dialog box will pop up.

Step 4 – Then enter the file name in “File Name”, now in “Save as type” drop-down select the .xlsb format and click on “Save”.

This approach will enable them to excel file to reduce the size of the spreadsheet.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

#2 – Convert Unnecessary Formulas to Values

The next approach that can be used to reduce the excel file size is to convert unnecessary formulas into values. Below is the list of steps that one can use to understand the methodology.

Step 1 – Open the excel file, and select the entire worksheet or the number of columns to be converted from formulas to values.
Step 2 – Now press the “F5” key, the pop-up dialog box would appear. Then click on “Special”.

Step 3 – This will open up another dialog box as shown below, then click on the radio button for “Formulas” and click on Ok.

Step 4 – Now that all the cells containing formulas have been selected. Go to the Home tab and click on “Paste”.
Step 5 – Now select Values, once the dropdown appears when clicked on “Paste”. Then click on “Paste Values”.

Step 6 – Or, use a shortcut key CTRL+ALT+V, then a dialog box will appear as below. Select the radio button for “Values” and click “OK”.

This would convert the selected formulas into values, which will enable to reduce the file to reduce and make the spreadsheet lighter.

#3 – Compress Image

The next approach is to use compress the image in an excel file to reduce the workbook size. This method is essential when we have too many images contained in the workbook or worksheet which drives to increase the size of the excel file.

Step 1 – Click on the image you want to compress. Then go the “Format” tab in the ribbon area as shown below.

This is the picture tool that appears only when the image is selected.

Step 2 – In the “Format” tab select “Compress Pictures”.

Step 3 – A dialog box will appear after selecting the compress picture icon.

Step 4 – The option “Apply only to this picture” allows the user to compress only the image which is been selected. Deselecting this option will allow all the images in the workbook to get compressed. Also, select the option for “Email (96 PPI)” which is the minimum document size for sharing.
Step 5 – Click on Ok and save the workbook. This will reduce the size of the overall excel file.

#4 – Deleting Pivot Cache

The following approach will help reduce the excel file size by deleting the pivot cache while creating the pivot table from the source data.

Step 1 – Select the “Analyze” tab in the pivot table tools. This tab appears only when the pivot table in the excel file is selected.

Step 2 – Now click on pivot options dropdown followed by options.

Step 3 – Next to a dialog box would appear, go to Data tab.

Step 4 – Now deselect the checkbox for “Save source data with file”. This option will not create any pivot cache and enables to reduce the excel file size.

Step 5 – Now select the Option for “Refresh data when opening the file”, this will ensure that the pivot is refreshed as soon as the excel file is opened. If the option is not selected then the pivot table needs to be refreshed manually which would lead to generating pivot cache.

Step 6 – Then click OK. This will ensure that the excel file size is reduced.

Things to Remember

XLSB file format tends to open and save at a much faster rate, however, there are even a few things to consider where macro enabled and query-based excel files should not be converted to XLSB as they might cause functional errors while operating as XLSB.
Also if the excel file size for XLSX format is 5MB to 7MB can be left as is, only if the XLSX file size zooms to double or triple digits in MB’s. Then can the excel files can be considered to reduce the file size using any of the best-suited approaches.
There are other approaches such as using the compressed zipped folder, this is done by doing right click on the excel file, go to send and then click on a compressed zipped folder. This will reduce the file size while sharing. This can be also done by selecting multiple files and then compressing the files in a zipped folder.
Prefer pivot tables over formulas used to summarize data for generally larger data sets, this will have less impact on the file size. Also if there is higher usage of formulas being made then change Automatic calculation mode to Manual mode in the formula tab. This will make the file open, making changes, updating the excel file, and closing the files much faster.