Excel 2007 : Pivot Table Grouping

**mattydalton** · 04-22-2010, 05:53 AM

Hi

I am trying to create a report to show dialling performance across dates, weekdays and hours but want a separate report for each. I have tried to create 2 separate pivot tables for this purpose but when I group one by day the one that is grouped by hour automatically changes to day too! I think they must be linked somehow but can't see how to stop them being linked. It doesn't help that I am using a German version of Excel and am not particularly good at German. Can anybody help?

Thanks

Matt

**DonkeyOte** · 04-22-2010, 06:05 AM

For independent grouping you need to create the 2nd Pivot independent of the first - ie when creating the 2nd and you're prompted along the lines of "do you want to use the 1st Pivot as basis for 2nd " (to save memory) say no.

**mattydalton** · 04-22-2010, 06:07 AM

It didn't actually give me the option although I remember that feature from the older versions of Excel. I think I found the solution now anyway. Here it is for the benefit of anyone else who needs it:

Go to the data sheet and define your data range with a name, for example "Array1"
Now define another new name for the same data range, for example "Array2"
Change the data source of each pivot table so that one sources from Array1 and the other from Array2.

**mattydalton** · 04-22-2010, 06:09 AM

How do I edit the original post so that the prefix is "SOLVED"?

**DonkeyOte** · 04-22-2010, 06:26 AM

Originally Posted by mattydalton

It didn't actually give me the option although I remember that feature from the older versions of Excel.

XL2007 is no different to say XL2003 - after having specified the source range (step 2 in the Wizard) XL will prompt you if the source range is identical to that of another Pivot already in use.

Originally Posted by mattydalton

I think I found the solution now anyway.

Thanks for posting this.
To clarify, what you have done via the defined names is in line with the prior suggestion, ie you have created two entirely independent source ranges - one for each Pivot.

Originally Posted by mattydalton

How do I edit the original post so that the prefix is "SOLVED"?

To mark as solved click Edit on first post - Go Advanced - change Title Prefix to Solved.