Creating a Pivot Table from an Existing Pivot Table

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.

PivotTable Wizard Oddity

Suppose your workbook contains a pivot table. If you activate the PivotTable Wizard and immediately click Next, without changing and settings, you will get the message shown in Figure 8.18.

Figure 8.18. This message warns you about the file size implications of creating another pivot table from the same data source and asks if you want to use the same pivot cache in order to save memory and disk space.

However, if you activate the PivotTable Wizard and immediately click Finish without changing any settings, you will not get the warning shown in Figure 8.18. Instead, Excel will create a separate pivot cache for your new pivot table automatically.

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:

• Refreshing your data You will not be able to refresh one pivot table and not the other. Refreshing affects both tables.

• Adding a calculated field If you create a calculated field in one pivot table, your newly created calculated field will show up in the other pivot table's field list.

• Adding a calculated item If you create a calculated item in one pivot table, it will show in the other as well.

• Grouping or ungrouping fields Any grouping or ungrouping you perform will affect both pivot tables. For example, suppose you group a date field in one pivot table to show months. The same date field in the other pivot table will also be grouped to show months.

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.