Grouping Separate Pivot Tables

[Excel Ron]

I am having trouble with my pivot tables when it comes to grouping dates by years. I have made copies of the pivot table to use in two other areas. The problem is that on one I want to group the dates by year, next by year and quarter, next by year quarter for 2011. My problem is is that every time I change the grouping on one pivot table the other two pivot tables do the same grouping. Is there any way to disconnect this connection between the pivots so that I can have the pivots showing the three different date groupings?

Thanks!

 

[sr12345]

Grouping seems to be tied to the data set, and not the pivot table. Try this.

Let's say your dates are shown in the data table as column A. Add two columns to your dataset, which duplicate the date value. Label these columns Date2 and Date3. [in cell N2, for example, insert the formula =A2]

Set one pivot table using the "Date" data set, and group as you want.
Make a copy of that pivot table tab. Replace the "Date" field with "Date1", and group as you wish.
Make a copy of one the previous 2 pivot table tabs. Replace "Date" or "Date1" with Date2, and group as you wish.

Now, any changes you make to the Date, Date1, or Date2 fields don't impact the other pivot tables.

 

[Excel Ron]

Absolutely Genius!! Thanks so much!!

 

[Hermanito]

Another way to do this is as follows:

when you create your second, third, etc... pivot table, you normally get a message box asking if you want to reuse the same pivotcache (not those exact words) as the previous pivot table. If you answer 'no' there, your pivot tables will not be influencing each other when you group data...

imho a cleaner solution than the one sr12345 provided, because you don't need to perform tricks on your source data

 

[Excel Ron]

Where does it show you the pivotcashe screen? Is it when you make the pivot from the data source or copy and paste the data source? I have tried both ways and it doesn't work. I do like your suggestion as I really don't want to play with my data source since I will be pulling in data at the end of every month and will have to hassle with remembering to pull down my formulas.

Thanks!!

 

[Hermanito]

If you have created one pivottable from a certain datasource, Excel creates a pivotcache behind the screens. You cannot directly work with this pivotcache (in VBA you can, but that would take us too far in this case), but it is mainly based on the datasource.

When you create a second pivottable from the exact same datasource (you would need to select the identical range as you did for the first pivottable, there is no need to make a copy of the sourcedata), Excel recognizes this and proposes to reuse the existing pivotcache. In Excel 2003 this is shown to you as a messagebox at some point in the process of creating the pivottable. Basically Excel asks if it may reuse the existing pivotcache or not. If you do, you end up with pivottables that are linked together behind the screens to the same pivotcache, and thus also share groupings you might define on the data.
If you choose not to reuse the pivotcache, you will have truly independent pivottables, but your workbook filesize will be somewhat bigger because Excel needs to maintain a second pivotcache...

Try to create two pivottables as described above and Excel should pop up the messagebox I'm talking about... when adding more pivottables on the same sourcedata, you should always get that messagebox where you can choose to reuse an existing or create a new pivotcache...

 

[Excel Ron]

Worked!! Thanks so much!!