Hi,
Let's say I am trying to create a chart from employee data to find out the number of years employees have been in an organisation.
I am looking at 2 different groupings for the # of years spent
1) group by 1 year interval
2) group by 5 years interval
It does not matter how I create the pivot table and charts, when I create the second pivot table and chart the first table and chart default to the group settings of the second chart or vice-versa.
How I avoid that and have 2 separate groups/ views?
Thank you!
From what I understand, you need to un-share the Pivot Cache for both pivot tables. It is explained in this blog post by Mynda:
Excel Pivot Cache • My Online Training Hub
You need to intentionally duplicate Pivot Caches so that you can apply different groupings in pivot tables that use the same data.
It's about 4 minutes into the video.
Thank you! That worked. I was able to duplicate the pivot cache 🙂
However Alt + D + P did not invoke the pivot wizard for me ( I use Excel 2019 on windows 11).
Quick Access Toolbar > More Commands > All Commands and added the Pivot table and Pivot chart wizard to the QAT.
I’ve run into this exact wall before. The 'secret' is that you actually need to create a separate Pivot Table for each chart if you want them to show different filtered views. You can just copy and paste your first Pivot Table to a new spot - they’ll still share the same 'Pivot Cache' (so your file size won't explode), but now you can filter each one independently for your dashboard.
@seizal_casper
Thank you for your engagement to help resolving old questions in this forum. In this particular case, however, I believe your answer is not responding to the issue described by the OP. That is, being able to group (i.e. not filter) numerical headers in different intervals for two pivot tables based on the same data. In that case, you must not use a shared pivot cache and follow the instructions in the video (included in my earlier post).