Chapter 30: Analyzing Data with PivotTables
3030
FIGURE 30.9
This PivotTable shows sales by quarter and by year.Multiple Groups from the Same Data Source
If you create multiple PivotTables from the same data source, you may have noticed that grouping a
field in one PivotTable affects the other PivotTables. Specifically, all of the other PivotTables automati-
cally use the same grouping. Sometimes, this is exactly what you want. At other times, it’s not at all
what you want. For example, you might like to see two PivotTable reports: one that summarizes data
by month and year and another that summarizes the data by quarter and year.
Grouping affects other PivotTables because all of the PivotTables are using the same PivotTable “cache.”
Unfortunately, there is no direct way to force a PivotTable 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 ➪ Defined Names ➪ Name Manager.
When you create the first PivotTable, specify Table1 as the Table/Range. When you create the second
PivotTable, specify Table2 as the Table/Range. Each PivotTable will use a separate cache, and you can
create groups in one PivotTable, independent of the other PivotTable.
You can use this trick with existing PivotTables. Make sure you give the data source a different name.
Then select the PivotTable and choose PivotTable Tools Analyze ➪ Data ➪ Change Data Source. In
the Change PivotTable Data Source dialog box, type the new name that you gave to the range. This
will cause Excel to create a new PivotCache for the PivotTable.