Microsoft Office Tutorials and References
In Depth Information
Figure 18-22: A pivot table with two groups and subtotals for the groups.
Multiple Groups from the same data source
If you create 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 group-
ing. Sometimes, this is exactly what you want. Other times, it's not at all what you want. For example, you might
like to see two pivot table reports: one that summarizes data by month and year, and another that summarizes the
data by quarter and years.
The reason why grouping affects other pivot tables is because all the pivot tables are using the same pivot table
“cache.” Unfortunately, there is 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 giving multiple range names to the source data.
For example, name your source range Table1 and then 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
When you create the first pivot table, specify Table1 as the Table/Range. 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.
You can use this trick with existing pivot tables. Make sure that you give the data source a different name. Then
select the pivot table and choose PivotTable Tools⇒Analyze ⇒Data⇒Change Data Source. In the Change PivotT-
able Data Source dialog box, type the new name that you gave to the range. This will cause Excel to create a new
pivot cache for the pivot table.
Viewing grouped data
Excel provides a number of options for displaying a pivot table, and you may want to experiment with these op-
tions when you use groups. These commands are on the PivotTable Tools ⇒ Design ⇒ Layout tab of the Ribbon.
There are no rules for these options. The key is to try a few and see which makes your pivot table look the best.