How to Reduce Excel File Size (13 Methods that Work)!

Reducing large Excel file sizes involves some methods that optimize and compress the Excel file to make it smaller in size. In this way, you convert your Excel files to be used by users in faster and more lightweight ways.

Handling a large file is important as it takes a huge amount of time to transfer. A large file takes too much time to open. Any kind of change in a large file takes a long time to update. So, reducing file size is important. In this article, we will see how to reduce Excel file size. There are many methods of doing that. We will be choosing the best options here.

In our analysis, we use sample data from superstore sales. The sample was downloaded from this link https://community.tableau.com/docs/DOC-1236.

The original file size of this sample is 3,191 KB.

In this section, we will discuss 3 proven methods to reduce/compress large Excel file sizes. Let’s check them now!

1. Deleting/Clearing Blank Cells in Excel

After you complete a task in the Excel workbook, there are so many unused cells that have no usefulness in your workbook. Sometimes you accidentally do some formatting in these unused cells which actually increases your file size. Removing the formatting from these blank cells reduces your file size.

• If you want to select the entire blank cells just click CTRL+Shift+ ↓+ → together. The arrow signs indicate where you want to put your command.

• After selecting the blank cells, In the Home tab on the Clear option press Clear All. This will clear the cells.

There is another way of doing that.

• On the Home tab, Press Find & Select and then press Go To Special. 

• It will open up the Go To Special dialog box. On that box tick on the Blanks and press OK. This will automatically find the blank areas of your Excel workbook. After this clear the blank cells as we did earlier.

2. Check and Delete Unnecessary Hidden Cells

Sometimes unnecessary hidden cells are the reason for larger Excel files. You may accidentally hide some rows or columns of the Excel worksheet you are working on. Suddenly you realize that your file contains some hidden elements which you don’t require. All you can do is unhide the rows and columns of the Excel worksheet and see if you need them or not. If you don’t need them just delete them all.

3. Saving a File in Excel Binary Format

Saving the Excel file from  (.xlsx) format to Excel binary format (.xlsb) reduces the size of your file by around 40%. This is the easiest and most efficient way of reducing your file size. The procedure is given below.

• First of all, save your sample Excel file in (.xlsx) format in a specific folder.

• Then, save the same file in (.xlsb) format in the same folder in which you have saved the previous (.xlsx) formatted file.

• Now, the two files are saved in the same directory.
• Here, we will check the file size of the (.xlsb) file and compare it with the (.xlsx) format whether it is reduced or not.

We can see the (.xlsb) file contains 1,666 KB whereas the (.xlsx) file contains 3,191 KB. So, the file size is reduced on a large scale.

Excel Binary Workbook Pros and Cons

While saving a file in (.xlsb) format, one thing you should keep in mind is that, like everything, saving an Excel workbook in binary format brings some pros and cons.

Pros

• The Excel binary file uses noticeably less space while saving than (.xlsx) files.
• Loading binary data is faster than parsing text (.xml) files or (.xlsx) files.
• Macros and VBA code is fully supported.

Cons

• No Ribbon modification is allowed for (.xlsb) formats. You must convert back to
• (.xlsm)
• , make your Ribbon changes, and then back to (.xlsb).
• Not compatible with Excel 2003 and previous versions.
•  (.xlsb) is a binary file format, unlike the open  (.xml), (.xlsx), and  (.xlsm) files. Hence, you often won’t see your  (.xlsb) files working everywhere.

So, be careful while saving a file in binary format. Surely you can do this when the advantage it provides is much more than the demerits.

4. Checking Conditional Formatting

In Excel, Conditional Formatting visualizes data and makes a worksheet easier to understand. But sometimes we do mistakes while applying conditional formatting. Sometimes we apply formatting to an entire sheet or an entire group of rows or columns. To minimize the mistake, we can go to Conditional Formatting > Manage Rules and check the cell range of each of the rules. Again if we somehow apply conditional formatting in the Excel workbook and suddenly realize we don’t need that we can undo the conditional formatting by clearing rules from the entire worksheet.

5. Avoid Using Volatile Formulas

There are seven volatile functions in Excel which are RAND, NOW, TODAY, OFFSET, CELL, INDIRECT, and INFO. These formulas are recalculated whenever there is a change in the Excel file. If your worksheet has a lot of volatile formulas then the calculations become slow hence the file size is increased. So, the solutions are simple, we should avoid using volatile formulas. There are some alternatives to volatile formulas. They are given below

• Instead of the OFFSET function, we can use INDEX.
• Within a worksheet, the INDEX function can be used by using column numbers rather than letters to replace INDIRECT functions.
• There is no alternative for NOW and TODAY functions. But VBA code can be used to replace them.

Like for Now functions, we can use-

Private Sub Workbook_Open ()

Range("A1"). Value = Time

End Sub

For TODAY functions we can use

Private Sub Workbook_Open ()

Range("A1"). Value = Date

End Sub

• The RAND functions are not required most of the time. However, if we do require a new random number we could use a VBA function to generate it.

Private Sub Workbook_Open ()

Range("A1"). Value = Rnd ()

End Sub

• INFO and CELL are not particularly common formulas. They provide a system, workbook, and cell status information. Instead of using them, we can use the file path or cell color reference number using VBA.

6. Use Pivot Tables or Excel Tables

Instead of a series of formulas using Pivot tables or Excel tables is an efficient way to show your result.

7. Avoid Referencing Entire Rows or Columns

The SUMIF or VLOOKUP functions look for data in the entire columns or rows. Instead of referencing the entire data, we can refer to only a few cells if needed. Like-

Instead of using =SUMIF (A: A, $C4, B: B)

We can use =SUMIF (A1:A50, $C4, B1:B100)

Here we are referring to the 1^st50 cells of Column A.

8. Reduce Excel file size by deleting blank rows

You can reduce Excel file size by deleting blank rows.

9. Avoid Repeated Calculations

Let’s say you have a formula that contains “=$A$3+$B$3”, and you copy that formula to fifty cells. Here the total number of cell references is a hundred, despite the number of unique cells only being two. But if C3 has a formula which is =A3+B3, and you want to update those on 50 other cells, just by referring to cell C3 in those 50 cells you can actually make the number of reference 50, whereas your previous referencing required a hundred cells.

10. Commanding Excel Manual Calculation

This actually helps you to calculate faster in Excel. But sometimes in large Excel files doing manual calculations can also help you to reduce your file size.

11. Customizing Pivot Tables

Pivot Tables are created for quickly checking data or gaining an overview. These pivot tables may increase your file size if you don’t need them anymore. If you don’t require your pivot tables in your next calculations, you can do the following steps.

• 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’.

12. Reduce Excel File Size with Pictures

Sometimes when you add pictures to an Excel worksheet the file size of Excel automatically increases. You can reduce the size of the picture outside Excel or within Excel. If you want to do it within Excel, select the picture and right click on the mouse button, and select Size and Properties.

A Format Picture window will open up on the right side of the Excel worksheet. Now you can manually select the height and width of your picture.

You can manually resize your picture outside of Excel. There are so many procedures for doing that.

Read More: How to Reduce Excel File Size with Pictures (2 Easy Ways)

13. Reduce Excel File Size Without Opening

It is not always necessary to open the file or delete the file data. Excel allows you to reduce the file size without even opening the file.

# Formatting File from File Properties

After doing all the tasks in Excel it’s easier to compact the size of an Excel file from file properties. It’s a Windows feature and is not directly related to Excel. The procedure is given below.

• Right-click on the file.
• Select Properties.
• Next, Select the Advanced button.
• Finally, select “compress contents to save disk space” and press OK.

# Compress Excel File to ZIP/RAR

Many words processing documents can be compressed to 10 percent of their original size by using different compressed tools. It`s a good practice to share files by compressing them. It not only reduces the file size but also makes the file secure from viruses. Here we will be using WinRAR to compress the file. The procedure is given below.

• Right-click on your file.
• Press Add to archive.
• A dialogue box will come up to choose RAR/ZIP as an Archive format.

• A dialogue box will come up to choose RAR/ZIP as an Archive format.

Reduce Excel file size using online tools (Free & Premium)

There are several popular tools online that you can use to compress your Excel files. Most of the tools handle most of the Excel file formats. Here is a list of tools that you can use to compress your Excel files:

• Aspose.app // This online tool compresses XLS, XLSX, XLSM, and XLSB Excel files.
• Fileformat.app // This online tool compresses XLS, XLT, XLTX, CSV, XLSX, XLSB, and XLSM Excel files.
• Reducefilesize.com // This online tool compresses .xls, .xlsx, and .xlsm files (max 50 Mb).

How to reduce Excel file size for email

Before sending an Excel file to someone via email, you better compress the file. You can use the above-mentioned ways to compress the Excel file before sending it via email. This guide covers some more methods.

Why is my Excel file so large?

Excel files can be so large for these reasons:

• Unused data
• Presence of less important formulas and functions
• Hidden worksheets
• Extra charts and graphs
• Not using the right file format
• A large amount of source data

How to Zip an Excel file?

You can Zip an Excel file by:

• Using Windows Send command to Zip
• Renaming the file extension to Zip
• Applying a VBA code to Zip an Excel file

Conclusion

Most people find it easy to share small-sized files. As we can see there are many methods of doing that. Compressing file size also makes it easy for speeding up the Excel calculation. We can use these techniques separately or together to reduce our file size.

More Articles on Reducing Excel File Size

• Reduce Excel File Size with Macro (11 Easy Ways)
• [Fixed!] Excel File Too Large for No Reason (10 Possible Solutions)
• How to Reduce Excel File Size Without Deleting Data (9 Quick Tips)
• How to Compress Excel Files More than 100MB (7 Useful Ways)
• How to Determine What Is Causing Large Excel File Size