How to Reduce the File Size of Your Excel Workbook with 7 Easy Steps

Excel workbooks usually have an acceptable file size. But sometimes, they are unreasonably large. There are several reasons for a big file size coming with different way of how to reduce it. Depending on the content of your Excel workbook some methods will work better than others. So, let’s try them one by one.

Preparations: Check which worksheet causes the large file size

The important question: What part of your Excel workbook causes the large file size. Basically, there are several options:

1. Large data, usually worksheets with more than 100,000 rows and/or many columns.
2. Pictures: Do you have any images in your file?
3. Pivot Tables: Pivot Tables store the underlying data so (depending on the number of Pivot Tables) in worst case, each Pivot Table contains the data set.
4. Formatting: If sheets are formatted although cell ranges aren’t used, the file size might increase.
5. Others: Meta-data, other invisible contents.

For each of these types, the approach of reducing the file size is different. So how do you find out, which type(s) might be the case for you?

Either, you just go ahead and try the following methods. Or – and that’s the recommended way – you do it more systematically. We recommend you to check the file size of each worksheet. Then you just start with the largest worksheet and depending on the type, you just do the specific methods.

How do you get to know the file size of each worksheet? There is no simple built-in way in Excel. But our Excel add-in helps (7-day free trial without sign-up): Just press the “Reduce File Size” button and then click on “Check size of each worksheet”.

Method 1: Reduce the size of pictures

One common reason for large Excel files are pictures within the workbook. Fortunately Excel offers a function for compressing images: The resolution will be decreased and the invisible areas can be removed. Just follow these steps (the numbers are corresponding to the picture on the right hand side):

1. Select one image in your workbook.
2. Activate the Format ribbon which is now available (Mac: ‘Picture Format’).
3. Click on ‘Compress Pictures’ so that a new window opens for defining the compression settings.
4. If you want to compress all pictures in your current workbook remove the first tick. Excel can also delete the cropped areas of pictures if you’ve used the cropping tool.
5. Select the resolution. Maybe you try the highest resolution first and go down step by step. Monitor the impact on the file size by saving the workbook after each compression.
6. Click OK to confirm.

This method could reduce the file size – depending on your images – by app. 22% as for a test workbook.

Method 2: Remove the formatting from unused cell ranges

When you apply a cell format to whole rows or columns, Excel often needs a lot of disk space. You can try to delete all the unused areas below and on the right hand side of your data:

1. For rows: Select all rows below your last row with content.
2. Press Ctrl + ‘–’ on the keyboard for deleting the rows. Sometimes, this doesn’t remove the formatting. For making sure that the formatting is really deleted continue with step 3 and 4.
3. Click on the rubber button on the right hand side of the Home ribbon.
4. Click on ‘Clear All’.

Repeat these steps on every worksheet, also for the columns. Save the workbook afterwards for checking, if the file size is reduced.

This method could reduce the file size – depending on your formatting and style – by max. 94%.

Method 3: Zip or split the workbook

3a) Zip your workbook

If your Excel workbook contains large amounts of data, zipping the whole file after you are done editing often helps a lot. Basically, Excel files are zip files already (you can try it by just replacing .xlsx by .zip), but surprisingly, zipping the files again helps.

• On a Windows computer: Right click on the file in the Windows Explorer. Click on ‘Send to’ and then on ‘Compressed (zipped) folder’.
• On a Mac: Search for the file in the finder, right click on it and click on ‘Compress [filename]’.

This method could reduce the file size – again depending on your file – by app. 15%.

3b) Split the workbook

Depending on how urgent you need to reduce the file size of your workbook you might consider splitting the file: Divide the workbook into two or more files, for example the first workbook for all the inputs and one sheet for consolidating the inputs. The second workbook has the actual calculations based on just the consolidation sheet from workbook 1.

Method 4: Reduce the size of Pivot Tables

Pivot Tables sometimes consume a lot of space: They use a cache in which the data is stored. There are basically three options to reduce the size of Pivot Tables:

1. In many cases, Pivot Tables are created for quickly checking data or gaining an overview. Do you still need all the Pivot Tables in your workbook? If no, remove some and check the impact on the file size.
2. When creating several Pivot Tables with exactly the same source data, don’t create them from the scratch but rather copy them. That way you can make sure that the data cache is shared.
3. Try this option: Don’t save the source data with the file. Right click on the Pivot Table and then click on Pivot Table Options. On the data tab remove the tick from ‘Save source data with file’.

Two PivotTables with the same shared cache use app. 13% less space than two PivotTables with separate caches.

Method 5: Remove unused content

This is the most obvious step so you’ve probably considered it already: Do you have any unused contents which you can delete? Are there hidden sheets which are not needed any more? If there is anything you don’t need any longer (if you are not sure, save a backup copy of the file), please go forward and remove the sheets or cell ranges.

In case of worksheets, follow these steps:

1. Select the worksheets you don’t necessarily need any longer by holding the Ctrl key and clicking on each sheet.
2. Right click on any of them.
3. Click on Delete.

Method 6: Reduce the file size with Professor Excel Tools

Probably the most comfortable way of reducing the file size of your Excel workbook is with the Excel add-in ‘Professor Excel Tools‘. It provides the following functionalities:

• Create a list of all worksheets and their size.
• Delete all the unused areas.
• Compress or remove the pictures from the workbook.

Please use the free trial version for checking if it works for you (download button below).

Method 7: Use the XLSB file format

This is one of the easiest methods of reducing the file size and at the same time the opening time of Excel workbooks: Save your Excel file in the XLSB file format. Besides these two obvious advantages, the XLSB format has some (usually minor) disadvantages though: The main disadvantage of XLSB files is that they can contain VBA macros (for more information please refer to this article: XLSX, XLSM or XLSB: Which File Type Is the Best for You?)

Changing the file format of your Excel file is very simple:

• Click on “File” next to the “Home” ribbon.
• Click on “Save As” on the left side.
• On the bottom of the “Save As” window change the drop-down field “Save as type” from XLSX to XSLB.

The effect: You can easily reduce the file size by 70%. A quick test with a file containing mostly formulas and hard values showed a reduction of the file size between 60% and 70%. As you can see in the figure on the right side, it’s especially worth changing the file format for XLSX files larger than 10 MB. On the horizontal x-axis, you have the file size of a XLSX file. If you change the file format to XLSB, the file size reduces significantly, shown on the vertical y-axis.