Reduce File Size in Excel

Top 4 Methods to Reduce the File Size of Excel

Below are the four easy methods to reduce the file size of the Excel workbook:

  • Save File in Binary FormatConvert Unnecessary Formulas to ValuesCompress ImageDeleting Pivot Cache

Let us discuss each of the methods in detail –

#1 – Save File in Binary Format

We can reduce the Excel file size by converting the regular .xlsx format to .xlsb, the binary format. Thereby reducing the file size to the least possible.

This approach will enable them to Excel files to reduce the size of the spreadsheet.

  • Open excel file .xlsx format. Then click on the “File” tab. In the File Tab drop-down list in excel, choose “Save As” option. Now select “Browse” Option, then a dialog box may pop up. Then, insert the file name in “File Name.” Now, in the “Save as type:” dropdown, select the “.xlsb” format and click on “Save.”

#2 – Convert Unnecessary Formulas to Values

The next approach we can use 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, and the pop-up dialog box will 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, all the cells containing formulas have been selected. Next, 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 – Else, we can 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.”

It would convert the selected formulas into values, enabling us to reduce the file and make the spreadsheet lighter.

#3 – Compress Image

The next approach is to compress the image into an excel file to reduce the workbook size. This method is essential when we have too many pictures 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 “Picture Format” tab in the ribbon area, as shown below

It is the picture tool that appears when the image is selected.

  • Step 2 –  In the “ Picture 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 chosen. Deselecting this option will allow all the pictures in the workbook to get compressed. Also, select the option “Email (96 PPI): minimum document size for sharing.”Step 5 – Click on “OK” and save the workbook. It 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 PivotTable from the source data.

  • Step 1 – Select the “PivotTable 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, a dialog box may appear. Go to the “Data” tab.

  • Step 4 – Select the checkbox for “Save source data with file.” This option will not create any Pivot Cache and can reduce the Excel file size.

  • Step 5 – NowNow, 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, the PivotTable must be refreshed manually, leading to a Pivot Cache.

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

Things to Remember

  • The XLSB file format tends to open and save much faster. However, there are even a few things to consider where we should not convert macro-enabled and query-based Excel files 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 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. It is done by right-clicking the Excel file, going to “Send,” and then clicking on a compressed zipped folder. It will reduce the file size while sharing. We can also do this by selecting multiple files and then compressing the files in a zipped folder.Prefer PivotTables over formulas used to summarize data for generally larger data sets. It will have less impact on the file size. Also, if there is higher usage of formulas, then change the “Automatic” calculation mode to “Manual” mode in the “Formulas” tab. Consequently, it will open the file, make changes, update the Excel file, and close it much faster.

This article is a guide on how to Reduce Excel File Size. Here, we discuss the top four ways to reduce Excel file size: 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 OrderConsolidate in ExcelProtect Workbook in ExcelAutoRecover in Excel