How to compress Excel files

by | Jul 15, 2021 | Excel Tips & Tricks

How to compress Excel Files

In this guide, we’re going to show you how to compress Excel files without using a third-party software.

Remove unnecessary data

Here are some tips for removing unnecessary data in your workbook.

  • Diagnose your worksheets and get rid of any irrelevant data. Consider working with multiple files instead of combining everything in the same workbook.
  • Complex calculations crank up the file size rapidly. Try to simplify your calculations.
  • Check the last cells in sheets and make sure that it is not too far from the used range. Press Ctrl + End to select the last cell.
  • Reduce the size and number of images and graphics. Try the Compress Pictures feature.
  • Remove any unnecessary formatting, including conditional formatting.

Here you can find detailed information about the methods above: How to reduce file size of your Excel workbooks

Remove Pivot Table Cache

A Pivot Table is a great tool managing data. The downside is that Excel keeps the cache data in the same file. This cache may increase the size of your file based on the complexity and data count in the Pivot Table. You can compress your Excel file by turning this feature off.

Apply the following steps to disable caching:

  1. Click on your Pivot Table.
  2. Activate the contextual Analyze tab in the Ribbon.
  3. Click Options in the PivotTable.
  4. Activate Data.
  5. Uncheck Save source data with file.
  6. (Optional) Check Refresh data when opening the file for auto-update.
  7. Click OK to apply.

After these steps, save your file and see if there is size difference.

Save as Excel Binary File Format (xlsb)

Excel's XLSX and XLSM file formats are actually compressed file packages. Excel keeps the data in XML files and compresses them, just like ZIP files. The Excel Binary File Format (xlsb) - like old XLS - can mean considerable size reduction.

The disadvantage of an XLSB file is that you cannot know if the file contains a macro or not, and macros can include malicious code.

How to compress Excel Files 02 - XLSB

Bonus: Compress Excel Files like any file

You can obviously ZIP your Excel file. However, since XLSX and XLSM file formats are already compressed file packages, you can't achieve substantial file size reduction by compressing an Excel file, which is already compressed. On the other hand, in some cases, you can get about 10% to 15% reduction.

If you do not have a third-party compressing software, you can use the built-in tools in your OS.