Pinterest Pixel

Reducing file memory

When working with Pivot Tables, sometimes we do not even notice that our data sources have grown... read more

Bryan
Posted on

Overview

Reducing file memory | MyExcelOnline Reducing file memory | MyExcelOnline

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:

DOWNLOAD EXCEL WORKBOOK


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

Reducing file memory

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

Reducing file memory

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

Reducing file memory

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

Reducing file memory

 

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.

See also  Move an Excel Pivot Table

Go to File > Save As

Reducing file memory

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

Reducing file memory

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

Reducing file memory

 

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

Reducing file memory

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

Reducing file memory

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

Drag the SALES REGION to Columns

Reducing file memory

It is working as expected, amazing!

Reducing file memory

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.

See also  Pivot Filter by Values - Top or Bottom %

Reducing file memory

 

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

Reducing file memory

Now the data table is back again!

Reducing file memory

 

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.

Reducing file memory

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

Reducing file memory

STEP 3: Select Formulas and click OK.

Reducing file memory

This will highlight all cells containing formulas!

Reducing file memory

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

Reducing file memory

STEP 5: Right-click and select Paste Special.

Reducing file memory

STEP 6: Select Values and click OK.

Reducing file memory

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:

If you like this Excel tip, please share it
Reducing file memory | MyExcelOnline Reducing file memory | MyExcelOnline
See also  How to Lock the Excel Pivot Table | A Detailed Tutorial
Reducing file memory | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...