How to Make an Excel Document Smaller and Run Faster - TM Blast

How to Make Excel Smaller and Run Faster

Working with large sets of data with pivot tables, slicers, graphs, macros, formulas, and more can definitely make your excel document larger than it needs to be. The problem with a larger excel document is that it takes up more of your space on your hard drive or cloud, it takes longer to open on both desktop and a mobile device, and it can be very slow in performing simple tasks like filtering out the data or using tools like a slicer. This becomes especially true when you are dealing with hundreds of thousands of rows of data. In this blog post, I am going to show how to change the format of the file to make the document run faster. I am also going to show how removing formulas from a document will cut the file size. Let’s get started.

 

Original Document

 

This is my current Google Analytics report that I have in excel. As you can see, we are looking at close to 600 kb just for a document. This document only has 12 thousand rows of data, so this is going to become a nightmare if we add more results to it every month.

 

very large excel file

 

How to Save an Excel Document as a Binary File Type

 

By default, your excel file will probably be a workbook. This setting for the average user is fine, but if you are looking to speed up the efficiency of the document, you want to save it as a binary file. To do this, you simply need to go to the save button and change the type to a binary workbook. Here is a picture below that shows what you should be saving it as.

 

how to save an excel document as a binary file

 

After I have saved the document, I already cut the file size in half which is incredibly since I did not really do anything. If you are interested in learning more about this file type, you can check out this article right here about binary excel file types.

 

excel binary much smaller than traditional excel document

Remove Your Formulas

 

Formulas and macros on your document will take up space. So how do we get around this you might be thinking? Well, we are going to have a second document that will act as our data dump file that will include all of our formulas. From there, we can copy over all of this information and we can  save it as a value. Here is an example where I have this column that has this excel formula.

 

excel file with a formula

 

As I mentioned before, I have about 11 thousand rows of data with this formula. If I simply save all of this as a value by copying everything in excel, I can save some space. As a note, you can do this if you don’t mind manually creating the formula every time and then saving it as a value.

 

a bit smaller excel document after removing the formulas

 

Some Other Ways To Make Excel Run Faster

 

There are definitely some more ways to make excel run even faster. You can connect your data from an external source to make the dashboard document even smaller You can change the data source on a pivot table to only grab the data from the last row of data not look at 1 million rows of data. You can make sure that your pivot tables are all connected so you don’t have each pivot that are all dynamically changing which will slow up the efficiency of the document. If you are really ambitious, you can check out some tutorials on how to run SQL based rules for your excel document.

1686 Massachusetts Avenue Apartment C, Cambridge Massachusetts 02138
877-425-2141