Reduce Excel File Size

Reduce File Size in Excel

For reducing excel file size, saving the spreadsheet in the default file format (.xslx) won’t work; instead, it should be stored as a binary workbook (.xslb). Also, image compressing, converting irrelevant formulae to values and clearing the pivot cache will be helpful.

Top 4 Methods to Reduce the File Size of Excel

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

1. Save File in Binary Format
2. Convert Unnecessary Formulas to Values
3. Compress Image
4. 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.

This approach will enable them to 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 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 buttonRadio ButtonIn Excel, radio buttons or options buttons record a user's input. They can be found in the developer's tab's insert section. read more for “Values” and click “OK.”

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

#3 – Compress Image

The next approach is to use compress the image into 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 to 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 has 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 tablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it. read more tools. This tab appears only when the pivot table in the excel file is selected.

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

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

• Step 4 – Now deselect the checkbox for “Save source data with the 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 a pivot cache.

Step 6 – Then click on the 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 MBs. 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 also be 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.

Recommended Articles

This has been a guide to Reduce Excel File Size. Here we discuss the 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 –

• Excel Reverse Order
• Consolidate in Excel
• Protect Workbook in Excel
• AutoRecover in Excel