SOLVED

Pivot Table grouping defaults to Month when I add data to data source

Copper Contributor

I have a Pivot Table which is grouped by Year.  When I add new records to data source, then hit refresh in Pivot Table, the grouping defaults to Month.  I would like it to remain by Year.

I tried the 'Disable automatic grouping of Date/Time columns in PivotTables' but that did not work.

Any suggestions?

 

Thank you,

3 Replies

@Basiliorio

 

Odd, it should be fine.   Can you add a screenshot of your Group By box and the Pivot Table fields

 

image.png 

best response confirmed by Basiliorio (Copper Contributor)
Solution

@Basiliorio 

Hi

There is also a possibility that you created another Pivot Table using the same Cache memory with a different grouping. In such case, excel attempts to save memory and it uses the same Pivot Cache and accordingly messes up the first Pivot with a different grouping.

if so, the problem can be fixed by using a FRESH copy of the pivot cache. I explain that in this tutorial:

https://www.youtube.com/watch?v=qebR0Bfe-9g

 

Hope that helps

Nabil Mourad

I chose the PowerPivot option and it appears to have worked. When I add or remove data to the source table, it does not change the grouping. It just adds the correct numbers grouped by Year.
Thank you,
1 best response

Accepted Solutions
best response confirmed by Basiliorio (Copper Contributor)
Solution

@Basiliorio 

Hi

There is also a possibility that you created another Pivot Table using the same Cache memory with a different grouping. In such case, excel attempts to save memory and it uses the same Pivot Cache and accordingly messes up the first Pivot with a different grouping.

if so, the problem can be fixed by using a FRESH copy of the pivot cache. I explain that in this tutorial:

https://www.youtube.com/watch?v=qebR0Bfe-9g

 

Hope that helps

Nabil Mourad

View solution in original post