How to make a self-contained pivot table

A pivot cache is what makes exploring data with a pivot table fun and snappy.

Let’s take a look.

Here we have an Excel table that contains almost 3000 rows.

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 with the pivot cache.

We can prove this by changing the first row of the source data.

Let’s 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 the source data will reduce the file size significantly.

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