Microsoft Office Tutorials and References
In Depth Information
Tip 82: Creating Pivot Tables with Multiple Groupings
Creating Pivot Tables with Multiple
If you’ve created multiple pivot tables from the same data source, you may have noticed that
grouping a field in one pivot table affects the other pivot tables. Specifically, all the other pivot tables
automatically use the same grouping. Sometimes, this is exactly what you want. Other times, it’s not at all
what you want. For example, you may want to see two pivot table reports: one that summarizes data
by month and year, and another that summarizes the data by quarter and year.
The reason grouping affects other pivot tables is because all the pivot tables are using the same pivot
table cache. Unfortunately, there’s no direct way to force a pivot table to use a new cache. But there is
a way to trick Excel into using a new cache. The trick involves assigning multiple range names to the
For example, if your source range is named Table1 , give the same range a second name: Table2 . The
easiest way to name a range is to use the Name box, to the left of the Formula bar. Select the range,
type a name in the Name box, and press Enter. Then, with the range still selected, type a different
name, and press Enter. Excel will display only the first name, but you can verify that both names exist
by choosing Formulas➜Define Names➜Name Manager (see Figure 82-1).
Figure 82-1: A range has two names.
When you create the first pivot table, specify Table1 as the Table/Range in the Create PivotTable
dialog box (see Figure 82-2). When you create the second pivot table, specify Table2 as the Table/Range.
Each pivot table will use a separate cache, and you can create groups in one pivot table, independent
of the other pivot table.