4 Tricks To Reduce Excel File Size

Posted by edwards paul on February 8th, 2021

Does your Excel workbook is responding to slow due to its excessive file size? Since Excel is not a web-based tool so to collaborate and work with this you need to send an Excel file via email.  We all know that it’s quite tough to share large Excel file.

Through this tutorial, I am going to share some quick tricks on how to reduce Excel file size so try them out….!

How To Reduce Excel File Size?

Trick 1# Remove Unnecessary Worksheets/Data

It is the best and easiest trick to reduce Excel file size.

If your Excel workbook contains such worksheets that you don’t need any more then it’s better to remove it. As soon as you delete excel worksheets this will instantly reduce Excel file size.

Trick 2# Convert to Binary Format (XLSB)

The second trick is to save the Excel file in the XLSB format. All you need to do is just save the Excel workbook in .xlsb format.

Here is the complete step on how this is to be done. In the following step will tell you how to convert the saved Excel workbook to binary format.

• First of all, choose the file showing the excessive size issue. For that Follow this path: File > Save As> Browse.
• Now in the opened Save As dialog box goes to the save as type tab.
• From the opened menu choose the Excel Binary Workbook (.xlsb) option.

You may also like: How to recover corrupted Excel file

Trick 3# Remove Unnecessary Formulas

Sometimes excessive usage of formulas also bloats the workbook file size.

So if you are having such a data set in which you don’t need any formulas then it’s better to convert these formulas into values. This will ultimately help in reducing excessive file size.

Follow the below-given steps to convert Excel formulas into values:

1. Choose the complete worksheet or dataset.
2. Hit the F5 key from your keyboard. This will open Go To dialog box in which you have to hit the ‘Special’ option tab.

3. From the opened window of Go To Special dialog box, choose the Formulas option.

4. Tap the OK button.

The above step will choose entire cells which are having formulas in it. Now it’s time to convert formulas into values for this just follow the below-given steps:

• First of all, copy the selected cells by pressing the ctrl+ C button from your keyboard.
• Hit the Home tab.
• From the Clipboard group, tap the Paste option.
• From the Paste Value sections choose this icon.

With the above step, you can convert all the cells having formulas into values.

Trick 4# Keep The Source Data And Delete Pivot Cache

After completing all your work in a pivot table, you can make such settings by which Excel will only save the source data and resulting pivot table, not the Pivot Cache.

You can recreate the pivot cache whenever you required it. Deleting the pivot cache reduces Excel file size.

Here is the complete step to setup Excel file is such a manner that it will NOT save the Pivot Cache:

1. Choose any of the cells in the Pivot Table.
2. Hit the Analyze tab.
3. Tap the Pivot Table ‘Options’ icon.
4. From the opened Pivot Table Options dialog box, you have to hit the Data tab.
5. From the pivot table data section, uncheck the ‘Save source data with file’. After making such changes you will see that from now on the Pivot Cache is not saved when you save the workbook.

6. Select the option ‘Refresh data when opening the file’.

This option ensures that when you open the workbook, Pivot Table is been refreshed and Pivot Cache is automatically get generated.

If in case you left this option unchecked then you need to manually refresh the pivot table for creating up the pivot cache.

7. Hit the OK button.

After doing all this, when you try to save the Excel file with the Pivot table, you will notice your Excel file size is reduced now.