Stop all the pivot tables applying the same grouping method

You’ve built your pivots, they work great so you create a few more so that your dashboard can show the information in different groupings. Perhaps a daily pivot chart to show the trend and then the same data shown monthly for reporting purposes. The problem is that when you change the one pivot’s grouping, to say monthly, all the other pivots also change to monthly. You can’t seem to have different grouping methods on the same field over many pivots.

Unlink the pivot tables

So how do you stop all the pivot tables applying the same grouping method. They appear to be linked in some way so how do you unlink it?

Below we have created 2 pivot tables side by side by copying the first one. For the second one we want to group it into weeks (see how on Pivot Table Course).

Stop all the pivot tables applying the same grouping method

The problem is, when you change the grouping on the second one, the first one also changes as shown below. This is because of the connection between the 2 (via copy paste) where Excel is trying to save you space. So in order to have the grouping be different on the pivot tables you need to disconnect them.

Stop all the pivot tables applying the same grouping method

Disconnect pivot tables on setup

In order to stop this you need to change the way you setup the second table. Instead of copy and pasting the pivot table you need to access the old pivot table setup menu. To do this you click on a cell outside the first pivot and then click

  • ALT, then
  • D, then
  • P

The following will appear.

Stop all the pivot tables applying the same grouping method

Make your choices and click next. You need to re specify where the data is. Click next

Stop all the pivot tables applying the same grouping method

Disconnecting the pivots- the most important step

The next step is the most important. This dialogue box below warns you that the data you have chosen is the same as another pivot tables data. It asks if you want to use the existing report thereby taking less space. You need to click NO.

Stop all the pivot tables applying the same grouping method

Now choose where you want to place the pivot.

Stop all the pivot tables applying the same grouping method

When you go through the process of grouping this time, you will see that it allows the 2 grouping types to exist on the same source data. If you are unfamiliar with grouping dates into months, weeks etc directly within a pivot table, have a look at the Pivot Table Course.

Stop all the pivot tables applying the same grouping method