If you create more than one pivot table from the same data source, and you have created a group of items in a pivot table, this group will automatically be copied in another pivot table. All the other pivot tables will use this same group. Sometimes, this is exactly what you want. Other times, it’s not at all what you want. For example, you may need two pivot table reports: one pivot table will summarize data by month and year, and another pivot table will summarize the data by quarter and year. See the following figure.
All the pivot tables use the same pivot table “cache”. This is why grouping in a pivot table affects other pivot tables. This is unfortunate for us that, we can’t directly force a pivot table to use a new cache. But we can use a trick to force Excel into using a new cache. The trick is we shall give multiple range names to the source data.
For example, we shall name our source data range Table1, and then we shall give the same data range a second name: Table2. You can use the Name box to name a range, it is the easiest way. Select the data range, name in the Name box, and press Enter. Then, with the data range still selected, enter a different name in the Name box, and press Enter. Only the first name will be displayed in the Name box if you select the data range, but you can verify that both names exist for this data range by choosing Formulas ➪ Define Names ➪ Name Manager.
When you will create your first pivot table, you will specify Table1 as the Table/Range. When you will create your second pivot table, you will specify Table2 as the Table/Range. Each pivot table will now use a separate cache. So you can create independent groups in one pivot table, that will not affect the groupings in other pivot tables.
Take a look at the following screenshot. There are 730 rows in this data range and we shall create two pivot tables but each pivot table will have independent groupings. The first pivot table will be grouped by month and by year, and the second pivot table will be grouped by quarter and by year.
Download this sample file where we have done this example
Creating a pivot table from Table1
Select the data range A1: B731 and enter Table1 in the Name box, and press Enter. Data range A1: B731 is now named Table1.
Then, with the data range still selected, enter a different name Table2 in the Name box, and press Enter. Only the first name will be displayed in the Name box if you select the data range, but you can verify that both names exist for this data range by choosing Formulas ➪ Define Names ➪ Name Manager.
Now select any cell in the data range A1: B731 and choose INSERT ⇒ Tables ⇒ PivotTable. Create PivotTable dialog box will appear. Enter Table1 in the Table/ Range field and E3 in the Location field. The location field will be activated when you will choose Existing Worksheet radio button. Then click OK.
After clicking OK in the Create PivotTable dialog box, a pivot table skeleton will be placed in the same worksheet in cell E3. Place Date field in the Rows area and Sales field in the Values area in the PivotTable Fields task pane. Right-click on any cell under the Row Labels column in the pivot table, a shortcut menu will appear, and choose Group from the options in the menu. A grouping dialog box will appear and choose Months and Years from the By list and click OK.
You will get a pivot table like the one shown in the following figure.
Creating the pivot table from Table2
Use the above steps to create a pivot table. Just use Table2 as Table/ Range in Create PivotTable dialog box and choose Quarters and Years from the By list in the Grouping dialog box.
You will get two pivot tables as shown in the following screenshot.
Changing Data source of existing Pivot Table
You can also change the data source o the existing pivot table. You have to make sure that you have given your data source e different name. Then select the pivot table and choose PivotTable Tools ➪ Analyze ➪ Data ➪ Change Data Source. Change PivotTable Data Source dialog box will appear, type the new name that you gave to the range in the Table/ Range field. Thus you can create a new pivot cache for the existing pivot table. Observe the following figure.
Happy Excelling 🙂