If your workbook already contains one or more pivot tables, you will be given the option of using another pivot table as your data source. This means that the PivotTable Wizard dialog box, shown in Figure 8.17, will enable the Another PivotTable or PivotChart Report selection.
Figure 8.17. You have the option of using another pivot table as the data source when your workbook contains one or more pivot tables.
Unfortunately, the PivotTable Wizard does not adequately explain what this means exactly.
When you create a pivot table, you are storing a snapshot of your entire dataset in a pivot cache, or a memory subsystem in which your data source is duplicated for quick access. Each pivot table report you create from a separate data source will create its own pivot cache. The issue is that every pivot cache that is created increases your memory usage and file size.
If your workbook already contains a pivot table, Excel tries to save you memory and disk space by giving you the option of using the existing pivot cache. When you select the option of using another pivot table as your data source, as shown in Figure 8.17, you are telling Excel not to create a separate pivot cache for your new pivot table. Instead, Excel will use the existing one in order to save memory and disk space.
By using the same pivot cache for multiple pivot tables, you gain a certain level of efficiency when it comes to memory usage and file size. However, there are some side effects to going this route.
Certain pivot table actions alter the content of the pivot cache. This means if you have two pivot tables using the same pivot cache, certain actions will affect both pivot tables. These include the following:
Although none of these side effects are critical flaws in the concept of sharing a pivot cache, it is important to keep them in mind when determining whether using a pivot table as your data source is the best option for your situation.