10 Tested Ways to Reduce Excel File Size Quickly

If you are an Excel user who works with a lot of data, creates reports, and shares files with others, I am sure you have faced a situation where your Excel files are large, and now you can’t share them with others.

Even if you have a large Excel file, it will also slow down, making it difficult to work with.

In this situation, when you have a large workbook, you need to know a few methods that can help reduce its size so that you can run faster and share it with others without any problem.

In this tutorial, we’ll look at simple ways to reduce Excel file size without losing much of the important information.

These methods make it easy to manage large workbooks and make them more efficient for everyday use. Let’s dive in and streamline your files!

Quick Tips to Reduce Excel File Size

Remove the Excessive Data
Convert Formulas into Values
Use Binary Format Workbook Format
Remove Source Data for Pivot Tables
Don’t Save the Pivot Table Cache
Compress Images
Zip the Excel Workbook
Remove Formatting
Remove External Links
Inserting Other Files as a Link

What Makes Excel Files Large

While working with data, you might realize that some of your actions contribute to a file’s size increase. That’s why it is also important to understand the steps you need to take to prevent a file from becoming too large.

I have done a few tests and found the following results of my own:

1. Large Data: Every 10,000 rows of data (with values in five columns) adds 500 KB to your file. That means the more data you add, your file will be larger.
2. Volatile Functions: Volatile functions are just like other functions, but they recalculate themselves and increase the file’s load.
3. Cell Formatting: Data formatting affects the size of the file. I have applied conditional formatting and cell color to 130000 cells, which adds around 150 KB to the file size.
4. Pivot Cache: A Pivot Cache increases your workbook size by approximately 4 MB for data of 130000 rows and five columns.
5. Images: When you have images in your Excel that you have added from your system or from any online source, each image increases the size of the Excel workbook.

If you want to check the exact reason your workbook makes the file heavy in size, you can use the 7-Zip application. Once you install the application, right-click on the workbook and then go to Z-Zip > Open archive.

From here, click on the “xl” to see the file’s content and which component is making it large.

You can see that you have all those different components when you open the xl folder.

• externalLinks – Include information about external data sources or workbooks referenced in your Excel file.
• embeddings – Hold all embedded media files inserted into the Excel workbook.
• pivotCache – Stores all the caches for pivot tables.
• pivotTables – Stores metadata and structural information about the Pivot Tables created in the workbook.
• worksheets – Stores data, formatting, and data layout on each worksheet.

Before this tutorial, we will follow some steps to reduce the file size of an Excel workbook. 

Steps to Reduce the Size of the Excel Files

Now, it’s time to follow some steps to reduce the size of your Excel workbook. You can use these steps to analyze your workbook and follow the steps that work for you.

1. Remove the Excessive Data You Have in the Workbook (Impact Medium)

As I shared earlier, when you have large data in a workbook, the size increases automatically. Now, in this case, you need to consider if you can delete the part of the source data that is no longer required.

As you can see in the example below, we have data with 130,000 rows, and the size of this file is around 7.45 MB.

In this data file, there is one column for the amount that I don’t need. I have the column for the total amount and taxes. So, if I need the amount in the future, I can calculate it back. That’s why I can delete this column to reduce the file size.

In the snapshot below, you can see that when I deleted that column, the file size was reduced to 6.71 MB. That column had 130000 data points, and removing it helped me decrease the size of the file by 0.74 MB.

In cases where you have a large Excel file and you don’t see any other way to reduce its size, removing the data that you think is of no use or that you might not need can help you make the file lighter in size.

2. Convert Formulas into Hard Values (Impact High)

The core of Excel is formulas and functions, and when you have large data and formulas that calculate values in many cells, that increase adds a lot to the size of that workbook.

Let me give you an example: In the last point, I showed how I deleted the amount column. Now, If I want to get the calculation of the amount back, I can create a simple formula that deducts taxes from the total amount.

I did that, and the result was shocking. It increased the file size from 6.71 MB to 8.17 MB, which is an increase of 21.75% for 130000 data cells with formulas.

You might be thinking here, what is the point of deleting a column first and then adding it back? Yes, you are right; it sounds funny. But my idea here is to prove a point.

If you have formulas, it’s always better to convert those formulas into hard values if you want to reduce the file size. After converting all the formulas into hard values, the file size has been reduced to the size it was at the start of the first point in this tutorial.

Converting formulas into values has a high impact on your workbook.

For this, you need only select that row, column, or range of cells and use the keyboard shortcut Ctrl + C to copy the values. Then, use the keyboard shortcuts Alt, H, V, V.

You can also use the VBA code below to convert all the data from the worksheet into values. But you need to make sure that you back up the file. Once you run the code, you won’t be able to undo that action.

Sub ConvertFormulasToValues()
    Dim ws As Worksheet
    Dim rng As Range

    ' Set the worksheet where you want to convert formulas
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your worksheet name

    ' Find the range of used cells in the worksheet
    Set rng = ws.UsedRange

    ' Convert formulas to values
    rng.Value = rng.Value

    MsgBox "All formulas have been converted to values."

End Sub

Note: Using a lot of Volatile functions can also add to the size of the file. This function updates its value whenever you change anything in your workbook. The more you use these functions, the more processing time a workbook will take.

3. Convert Your Excel Workbook into a Binary Format Workbook (Impact High)

In Excel, as you know, you have multiple formats for saving a file. Out of those formats, you have a binary format. When you save a file in (XLSB) format, it stores the data in a binary (machine-readable) format, which is compact.

In the example below, we have two identical workbooks. The first workbook is saved in binary format, and the second is saved in normal workbook format.

Both files have the same data and formatting but are very different in size.

The binary format is 4327 KBs, and the normal workbook is 7640 KBs. That’s a difference of 43%, almost half.  So, if you have data in an Excel workbook, which is huge, you can convert that file into (XLSB) format.

To do this, open the “Save As” dialog box (File > Save As) and choose the “Excel Binary Workbook” from the “Save as type” box. Then click OK to save the file.

One of the main reasons binary formats can save you a lot of space is that when you save an Excel workbook, it uses the XML code to represent all the elements in the workbook, which makes it heavier with each additional element.

On the other hand, XLSB uses the binary format to save the same elements and make it lighter.

4. Remove Source Data for Pivot Tables (Impact High)

When you create a new pivot table, Excel creates a pivot cache to store data snapshots. You can see in the example below that we have a file with a pivot table, and then we have a workbook, and then we have another workbook without the pivot table.

Now, if you have a pivot table and are sure you don’t need to create a new one from the same data, you can delete the source data. See the snapshot below. We have one more file where I deleted the source data, and we only have a pivot table.

When you delete the source data, the file size reduces by 65%, to equal the file size of the workbook that only had the source data.

5. Don’t Save the Pivot Table Cache

If you don’t want to delete the source data, you can ask Excel not to save the Pivot Cache with Excel. Yes, you can deactivate this setting. In the below example, we have two files; in the first file, we have the option disabled to save the pivot cache saved in the workbook, and in the second work, we have the option enabled.

Now, here is the point: when you create a new pivot tabled, this option is already enabled, and that’s why Excel saves the source cache within the workbook.

To deactivate this option, you can use these steps:

1. First of all, right-click on the Pivot Table.
2. Then, click on the “Options”.
3. Now, go to the “Data” tab.
4. In the end, untick “Save source data with file”.

Once you click on “OK” to save the setting, Excel won’t save the pivot table cache for that pivot table. This setting is a workbook-level setting that applies to all the pivot tables in the workbook.

6. Compress Images and then Upload (Impact Medium)

When you have images in your Excel workbook, that can make your Excel file quite heavy. Let me show an example; in the below snapshot, you can see I have some images with a total size of 522 KBs.

Now, when I add these images to my Excel workbook, it increases the size of the workbook by almost the size of all the images on my system.

One way to reduce the size of the workbook is to delete the images. But if you don’t want to do this, you can compress all the images using an online tool (like tinypng or compressor). When I upload these images, it can compress them up to 50% – 60%.

Once you compress these images, you can add them to your Excel workbook. The impact of this method depends on the number of images you have in your workbook.

Quick Tip: You can also change the settings within Excel to use low-resolution images by going to File > More > Options > Advanced > Images Size and Quality > Default Resolution.

7. Zip the Excel Workbook (Impact Low)

Zipping an Excel workbook might not help significantly reduce the file size, but it can help you lower it by 5% to 10%. When you zip, it can help you to share that file with anyone as a zip.

However, when it comes to Excel, there are a few points you need to understand. In the example below, you can see that I have two Excel files which have the same data but different file sizes.

This is because both files are saved in different Excel formats. The first file is saved in the old version (97-2003), and the second is saved in the latest version.

Now, let me show you what happens when we zip both files: When you zip the old version file, its size decreases from 8.7 MB to 2.7 MB, which is a reduction of 69%. However, when you zip the new version file, it only changes by less than 100 KB.

Now, if you ask me if zipping a file helps, it depends on which version of the workbook you are using. But there is one workaround: You can convert your new version file into an old version file and then zip it. This helps you share that file with others with a low file size.

8. Excessive Formatting (Impact Low)

For people who use a lot of formatting, they can tell you that they often face the problem of heavy Excel workbooks. Using font color, cell color, or even the data type formatting adds to the file size.

Applying cell formatting and conditional formatting can make a difference if you have a workbook like mine with 130000 rows of data. The example below shows that cell color and conditional formatting affect 130000 cells and add 150 KB to the file.

This impact does not seem great, but it makes a difference when you have data bigger than this. So, it would be best to use formatting to reduce the file size.

9. Link to External Files (Impact High)

Linking to external files can have a big impact on the file. The bigger the data you have reference to, the bigger the impact will be. Let me share an example; we have two files in the screenshot.

We have a link to the first file referring to the range A1:A1300001 in the second file. As you can see in the above screenshot, there is a difference of 1.1 MBs.

When you have a bigger range (you have referred to) or multiple formulas, the file will become much larger. You can convert all these formulas into hard values to deal with this.

If you have a small amount of data from another workbook you want to refer to, you can add that data to the current workbook in another worksheet and then refer to it.

10. Inserting Other Files as a Link (Impact Medium)

The file size increases when you insert a new object within an Excel workbook. In the below example, you can see we have two files. In the first file, we added a PDF file as a link, and then, in the second file, we had a PDF file that was directly inserted as a file.

In both files, you can see the difference of 654 KBs. This difference is almost the size of the PDF file we added to the workbook. So, whenever you need to add a file within an Excel workbook, you can tick mark “Link to file.”