How to compress Excel files

by | Jan 4, 0202

How to compress Excel Files

Excel spreadsheets are powerful tools for managing and analyzing data, but as your files grow, so does their size. In this guide, we'll explore practical methods to compress Excel files without the need for third-party software. Whether you're dealing with extensive calculations, images, or Pivot Tables, we've got you covered with easy-to-follow steps to streamline your data and optimize file size. Let's dive into the world of file compression and make your Excel experience smoother.

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 Tip: Compressing Excel Files Like Any Other File

While you might be tempted to ZIP your Excel file, our guide explores the nuances of compressing an already compressed file. Understand the limitations of achieving substantial reductions and discover potential gains ranging from 10% to 15%. For those without third-party compression software, we've got you covered with built-in tools in your operating system.

In conclusion, managing and compressing Excel files doesn't have to be a daunting task. By following these straightforward tips, you can optimize your spreadsheets for better performance and reduced file sizes. From cleaning up unnecessary data to exploring advanced formats like xlsb, empower yourself with the knowledge to excel in file compression. Remember, a leaner Excel file means a smoother experience, whether you're working on complex data analysis or sharing files with others.