Abstract
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 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. If we save this workbook with a new name, the new file is a lot 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 to recreate the source data. Just double-click the grand total, and Excel will create a new worksheet that contains the full set of source data.
