How to Reduce File Size with Macro in Excel

This guide will discuss three easy and simple methods on how to reduce file size with macro in Excel

Excel is a powerful tool containing many different functions and features we can use. Because of its many functions, the file size of an Excel file may become too heavy and large.

A heavy and large file would make the functions, features, and code run slower. Furthermore, it can also slow down and ruin your data and charts. Importantly, it would make it very difficult to share the file with other people if its size is too heavy and large.

For instance, Gmail only allows 25 MB files to be attached to an email. Any larger than that, we would need to upload it first to Google Drive and link it. And this takes too much time and difficulty.  

But reducing the file size would greatly help all of these issues. If we reduce the size file, it will improve the speed and quality of our Excel file. And it would make it easier to share the file with others.

Essentially, many methods exist to reduce the file size with macro in Excel. And it will depend on your preferences and file itself on what you are willing to do to reduce the size. 

Specifically, we will tackle three methods to reduce the file size with macro in Excel. Moreover, these are all easy to do and will most likely not affect your data set and presentation. 

Let’s take an example.

Suppose you want to send an Excel file over to your colleague for them to review. But you realized that the file size was too large. Since you need to send it over Gmail, your goal is to reduce the size to at least 25 MB to email it. 

To do this, you tried various ways to reduce the file size without impacting or damaging your data and charts too much. 

Awesome! Now let’s move on and learn how we can reduce the file size with macro in Excel.


Remove All Empty Cells, Sheets, and Formulas

There are times when we don’t make use of the entire spreadsheet. And we are left with dozens or sometimes even hundreds of empty cells. If we need to reduce the file size of an Excel file, the first thing we can do is to remove the empty cells.

Moreover, this is one of the guaranteed methods that will not affect your data sets or charts in any way. 

For instance, we have multiple sheets in the file. But all the important information, data, and charts are only on the first sheet, it would be best for us just to delete all the other sheets and retain only the first sheet.

To delete a sheet, simply right-click on the specific sheet you want to delete. Then, select Delete.Deleting empty sheets


Now another thing we can do is delete empty cells. Basically, we can just right-click on the empty row and click Delete.Deleting empty rows


But if there are several empty rows of cells, it would be easier to just delete them all at once. To do this, select all the empty rows. Then, go to the Home tab and click on the three dots. 

In the dropdown menu, select Delete and click on Delete Sheet Rows. Additionally, there is also an option to delete columns. So simply select the empty columns and select Delete Sheet Columns.Deleting multiple empty rows


Clear Unnecessary Formatting

Another feature that adds to the file size in Excel is the data formatting. For example, the borders, background color, and font style. These all impact the file size. However, it does not reduce the file size that much. But, all methods are welcome, especially in desperate times.

If you applied numerous data formatting across the file, it would be tedious to remove all of them. But, we can simply remove the data formatting all at once. 

To do this, we first need to select the data set we want to remove the formatting of. Then, go to the Home tab and click on the three dots. In the dropdown menu, click Clear under Editing. Finally, click Clear Formats. Reduce File Size Macro Excel


Changing The File to a Compressed ZIP File

The last method we will try is something we need to do outside of Excel. Since the file is too large, we will compress the file to reduce the size. Straight to the point, right? Furthermore, this is a very easy and simple method to do that will give immediate results. 

Firstly, we need to save the Excel file to our computer to compress or zip it. Click File. Then, go to Save As and select Download a Copy.Reduce File Size Macro Excel


After saving the file, find the file on your computer. Then, simply right-click and select Compress to ZIP file.Reduce File Size Macro Excel


Afterwards, a ZIP archive will be downloaded to your computer, where you can extract the Excel file to a compressed folder. Otherwise, depending on your computer, you may get the compressed ZIP folder immediately. Reduce File Size Macro Excel


You can make your own copy of the spreadsheet above using the link attached below. 

And that’s pretty much it! Now you have learned three easy methods to reduce the file size with macro in Excel. Moreover, these are easy methods that will most likely not affect the necessary and important parts of your Excel file. 

Are you interested in learning more about what Excel can do? You can now use the FV function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.


Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.


Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like