Reduce Excel File Size

How to Reduce Excel File Size?

Excel file size usually varies based on the content in the workbook, this makes the file sharing very challenging either through outlook email, share point or any other source of sharing applications. The contents in the excel workbook which make it really heavy and increases in size are generally the sizes of the data, a number of formulas applied for the length of the data, images, graphics, etc. This contributes to the slow opening of the excel files, time taking, consumes more time during any modification or changes to be done, also adds pain while working on share drive other than on desktop or on a local drive. In this article, we will learn the top ways to reduce the excel file size.

Top 4 Ways to Reduce File Size of Excel Workbook

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

#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. In order to reduce the file size follow the below given steps.

Step 1:- Open excel file .xlsx format.

Step 2:- Then click on the file tab. In the File Tab drop-down list 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 the excel file to reduce the size of the spreadsheet.

#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 “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.

The above steps will help select all the formula cells in the respective worksheet.

Step 4:- Now that all the cells containing formulas have been selected. Go to 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 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 which appears only when the image is selected.

Step 2:- In “Format” tab select “Compress Pictures”.

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

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.

Click on Ok and save the workbook. This will reduce the size of the overall excel file.

#4 – Deleting Pivot Cache

The following below 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 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.

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 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 are higher usage of formulas being made then change Automatic calculation mode to Manual mode in the formula tab. This will make the file opening, making changes, updating the excel file and closing the files much faster.

Recommended Articles

This has been a guide to Reduce Excel File Size. Here we discuss top 4 ways to reduce the file size of excel – 1)Save Excel File in Binary Format 2) Removing Formulas 3) Compress Image 4) Remove Pivot Cache. You can learn more about excel from the following articles –

• Methods to Shade Alternate Rows in Excel
• Excel Reverse Order of Data
• What are Contour Plots in Excel?
• Paste Values in VBA 
• XOR in Excel
• How to Consolidate in Excel?
• Using Toolbar on Excel
• How to Protect Workbook in Excel?
• How to AutoRecover in Excel?