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

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

Reducing file memory | MyExcelOnline

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

Reducing file memory | MyExcelOnline

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

Reducing file memory | MyExcelOnline

 

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

Reducing file memory | MyExcelOnline

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

Reducing file memory | MyExcelOnline

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

reducing file size

 

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

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

Reducing file memory | MyExcelOnline

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

It is working as expected, amazing!

Reducing file memory | MyExcelOnline

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.

Reducing file memory | MyExcelOnline

 

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

Reducing file memory | MyExcelOnline

Now the data table is back again!

Reducing file memory | MyExcelOnline

 

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

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

Reducing file memory | MyExcelOnline

STEP 3: Select Formulas and click OK.

Reducing file memory | MyExcelOnline

This will highlight all cells containing formulas!

Reducing file memory | MyExcelOnline

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

Reducing file memory | MyExcelOnline

STEP 5: Right-click and select Paste Special.

Reducing file memory | MyExcelOnline

STEP 6: Select Values and click OK.

Reducing file memory | MyExcelOnline

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!

 

HELPFUL RESOURCE:

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

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn