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).
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.
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
The following will appear.
Make your choices and click next. You need to re specify where the data is. Click next
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.
Now choose where you want to place the pivot.
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.
Want to learn more about Microsoft Excel and using it when budgeting or forecasting? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.