Reducing file memory

When working with Pivot Tables, sometimes we do not even notice that our data sources have grown to large sizes already. And if your Excel workbook is running slowly, what should you do?

I have a couple of quick ways of reducing file memory of your workbook!

1. Save as Binary Workbook
2. Delete original data table
3. Delete unnecessary formulas

Let’s look at each of these tips one by one!

 

Don’t forget to download the Exercise Workbook below and follow along with us:

---

Let us go over our current setup first. Here is our Pivot Table:

And here is our data source, it’s a big table!

Now go to File > Info so that we can quickly check the original file size

It’s 45.7 MB. Let’s get to work and start reducing file memory of this workbook!

 

Method 1: Save as Binary Workbook

One quick way is to convert this into a Binary Workbook.  Storing this in the binary format is useful for large spreadsheets.

Go to File > Save As

Select Excel Binary Workbook (*.xlsb) as the File type. Click Save.

After you do that, go to File > Info and you will be surprised! It is now 25.9 MB!

 

Method 2: Delete original data table

If you prefer to keep the original .xlsx format, there is another workaround! Make sure to use the original Excel workbook file (.xlsx) with the big data table.

Right-click on the data table worksheet and select Delete

Click Delete. The drawback with this approach is we will not be able to refresh the Pivot Table

Even without the data table, the Pivot Table will still work! It is all thanks to the cache.

Drag the SALES REGION to Columns

It is working as expected, amazing!

Make sure to save the workbook. Now go to File > Info and it’s now only 11.3 MB!

Thus, this method easily helps you in reducing file memory.

 

What if you need the data table again? There is a cool trick by double-clicking on the Grand Total.

Now the data table is back again!

 

Method 3: Delete unnecessary formulas

If the source data contains formulas and you can easily convert them into values to reduce the size of the Excel file.

To convert formulas to values, follow the steps below:

STEP 1: Select the entire data table.

STEP 2: Press Ctrl + G to open Go to Special dialog box and click Special.

STEP 3: Select Formulas and click OK.

This will highlight all cells containing formulas!

STEP 4: Press Ctrl + C to copy these cells.

STEP 5: Right-click and select Paste Special.

STEP 6: Select Values and click OK.

This will quickly convert all formulas in your worksheet into values. Thus, reducing file memory of your Excel workbook and making your file work faster!

 

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here: