Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to make a self-contained pivot table

Tags 
Summary 
Each pivot table has a pivot cache, which is a true copy of the source data. In today's video tip, we look at how the pivot cache works, and how it allows you to remove the source data from your workbook and still have a functional pivot table.
Video Transcript 

When you create a pivot table, Excel creates a duplicate of the data and stores it in something called a pivot cache. A pivot cache is what makes exploring data with a pivot table fun and snappy. Because a pivot cache is a true copy of the source data, you can remove the source data from your workbook if you like.

Let's take a look.

Here we have an Excel table that contains almost 3000 rows. This is sales data, and each row represents one order, for one kind of chocolate, to one customer.

Using this data, let's build a pivot table and see what happens if we remove the source data.

The moment a blank pivot table is created, Excel generates the pivot cache.

As we add fields to the pivot table, we are actually working the pivot cache. We can prove this by changing the first row of the source data. We'll change extra dark chocolate to mint chocolate.

Back in the pivot table, we see no change. Mint chocolate is nowhere to be found.

However, when we refresh the pivot table, we see mint chocolate appear. That's because refreshing the pivot table rebuilds the pivot cache.

Now let's remove the data tab from the workbook.

At this point, you might think the pivot table would break, but that's not what happens. Thanks to the pivot cache, the pivot table works just fine.

Removing source data will reduce the file size significantly. If we save this workbook with a new name, you can see that the new file is far less than half the size of the original.

But what if you want to see the source data again?

In that case, you can simply use a pivot table's drill down feature. Just double-click the grand total, and Excel will create a new worksheet with the full set of source data.

Author 
Dave Bruns
Works like a dream! Thanks very much Dave Very helpful :) - Rhiannon
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course