How to Compress Excel Files and Reduce Their Size

Working with Excel spreadsheets often means dealing with large file sizes. As you add more data, formulas, charts, and other elements, the file size increases. Before long, your Excel files become bloated, slow to open and save, and difficult to email and share.

Luckily, there are several techniques you can use to compress Excel files and reduce their size, while maintaining all the data and functionality. In this comprehensive guide, I’ll walk you through the top methods for shrinking Excel files so they become more manageable

Why You Should Compress Your Excel Files

Here are some key reasons to consider compressing your oversized Excel files

• Smaller files are easier to share via email and cloud storage. Large files eat up storage space and are annoying to email back and forth. Compressing brings files down to more reasonable sizes.

• Compressed files open, save, and function faster. Less bloat means better performance. Big Excel files can be painfully slow. Compressing helps speed them up.

• It makes your files more organized and efficient. Trimming unused items tightens everything up. You reduce clutter while keeping what’s essential.

• You maintain full data and functionality. Unlike manually deleting content, compressing only removes extra bulk, not key components.

The benefits are clear. Next I’ll cover several techniques to effectively compress your Excel files.

Save as Excel Binary Workbook (.xlsb)

The XML-based .xlsx format that Excel uses by default is great for cross-platform flexibility. But it comes with overhead bulk.

Saving your workbook as a binary .xlsb file will immediately reduce its size, often by 25-50% or more. The compressed binary format functions the same while cutting down on the bloat.

To save as an Excel binary workbook:

1. Click File > Save As.
2. Click Browse.
3. In the dialog box, pick Excel Binary Workbook (.xlsb) as the file type.
4. Click Save.

That’s it! The smaller .xlsb file will open and work exactly the same. You can also make .xlsb the default format to always save space.

Delete Unused Worksheets and Data

One of the simplest ways to cut an Excel file’s size is by removing unnecessary sheets and data. The less actual data cells your workbook contains, the smaller the overall file size will become.

Look through all your sheets and delete any that are blank or contain content you know you’ll never use. Even consider trimming down overstuffed sheets by deleting unnecessary rows and columns beyond your real dataset.

Every bit of data you remove directly reduces the bulk of your Excel file. This is an easy “trim the fat” technique.

Convert Formulas to Values

Formula cells take up much more space than raw values. When you no longer need the formulas themselves, converting them to static values will compress your Excel files.

To convert formulas to values:

1. Select the formula cells you want to convert.
2. Copy them (Ctrl + C).
3. Right-click and select Paste Special > Values to paste just the formula results.

You maintain the data while eliminating the bulky overhead of the formulas themselves.

Compress Images

Excel files with lots of large images can balloon in size. But there are a couple ways to compress pictures and reduce their impact.

First, use image editing software like Photoshop to optimize and resize photos before inserting them into Excel. Resize overly large images to lower resolutions that still look decent.

Second, you can compress images already in your Excel sheet:

1. Select the image.
2. Go to Picture Tools Format > Adjust > Compress Pictures.
3. Set compression options and image quality.

Compressing removes unnecessary image data, downsizing photos while keeping them visually appealing.

Remove Pivot Table Cache

Pivot tables are powerful but they increase file size by creating a data cache. You can prevent this bloat by unticking Save Data Source with File in the pivot table settings.

With this setting disabled, new files save without the data cache. But existing files need the cache removed. Here’s how:

1. Select any pivot table cell.
2. Go to PivotTable Analyze > Options > Data.
3. Uncheck Save Data Source with File and click OK.

Now your file saves without the pivot cache bulk, letting you compress existing Excel sheets.

Delete Conditional Formatting Rules

Conditional formatting applies special formatting to cells, but each rule also adds some file size overhead. If you have conditional formatting you no longer need, deleting it can provide compression.

To remove conditional formatting:

1. Select the cells with formatting to remove.
2. Go to Home > Conditional Formatting > Clear Rules.
3. Confirm deleting the rules.

Use this to compress files after the formatting is no longer essential.

Zip Your Excel Files

Beyond compressing Excel file contents, you can manually compress the file itself. Zipping an Excel workbook makes it around 10-15% smaller instantly.

Here’s how to zip an Excel file in Windows:

1. Right-click the Excel file.
2. Hover over Send to.
3. Select Compressed (zipped) folder.

You’ll now have a compressed zipped version of your Excel file. Share this and have users unzip before opening.

Clean Up Formatting

Lots of manual formatting like fonts, colors, borders, etc. add file size overhead. While removing formatting usually won’t drastically compress Excel files, it can still help trim some size.

To clean up formatting for compression:

1. Select cells with formatting to remove.
2. Go to Home > Clear > Clear Formats.

This clears selected cells of manual formatting while keeping data intact.

Track Your Compression Progress

As you use these techniques, continually check your Excel file size to see compression progress. Saving and reopening after applying multiple methods helps gauge their combined impact.

Aim to compress files enough for easy sharing by email and cloud drives. But avoid going overboard and needlessly compressing beyond reasonable sizes.

Find an optimal middle ground through selective use of these compression tactics only where beneficial.

Putting It All Together

The right Excel compression approach depends on your specific files and needs. But in general:

• Try binary format first since it’s the easiest change with big impact.

• Then delete unused items and convert formulas to trim data bulk quickly.

• Compress images making them as small as possible while retaining quality.

• For pivot tables, turn off cache saving.

• Zip the final file for that extra bit of compression.

And remember to check periodically to avoid over-compressing.

By combining several methods, you can effectively shrink the largest Excel files down to manageable sizes – maintaining full data and functionality while boosting performance.

Compressing Excel files takes a bit of work but pays off with easier sharing, faster operation, and streamlined files. Follow these tips to keep your Excel spreadsheets lightweight and nimble.