Pivot Tables Behind the Scenes

As you might have guessed, all of this awesome functionality is not free. It's important to know that pivot tables do come with a few file space and memory implications for your system. To get an idea of what this means, let's take a look at what happens behind the scenes when you create a pivot table.

When you initiate the creation of a pivot table report, Excel takes a snapshot of your dataset and stores it in a pivot cache. A pivot cache is nothing more than a special memory sub-system in which your data source is duplicated for quick access. Although the pivot cache is not a physical object that you can see, you can think of it as a container that stores the snapshot of the data source.

Each pivot table report you create from a separate data source will create its own pivot cache that will increase your memory usage and file size. The increase in memory usage and file size depends on the size of the original data source that is being duplicated to create the pivot cache.

Your pivot table report is essentially a view that gets its data solely from the pivot cache. This means that your pivot table report and your data source are disconnected.

CAUTION

Any changes you make to your data source will not be picked up by your pivot table report until you take another snapshot of the data source, or "refresh" the pivot cache. Refreshing is easychoose the red exclamation point on the pivot table toolbar.

The benefit you get by working against the pivot cache and not your original data source is optimization. Any changes you make to the pivot table report, such as rearranging fields, adding new fields, or hiding items, are made rapidly and with minimal overhead.