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