Preventing nested grouping when adding rows to pivot table in Excel
17,360
Solution 1
You can write code to create multiple copies of your pivot table, or just copy and paste manually, but you can't make a pivot table not group nested fields. That's the entire point of a pivot table, after all.
Solution 2
This one might be helpful Check it out:
You need to change both of these settings:
Related videos on Youtube
Comments
-
info_seekeR over 1 year
With the picture above in mind, I am trying to form pivots for each different age category and gender, using
Excel 2016
. Currently, however, when I add age and gender to therow
category ofpivot tables
, it forms a nested group by.Instead, I need to have un-nested/distinct pivots for the genders and age categories.
Is there anyway to achieve this in Excel?
-
Rory almost 5 yearsThat's how pivot tables work. Row fields are always grouped within any other row fields to the left of them. It sounds like you need separate pivot tables.
-
info_seekeR almost 5 yearsIndeed, separate. But that calls for a lot of manual work, and I was hoping there is some way to automatically achieve it
-
Rory almost 5 yearsCopy/paste the pivot and swap the row field.
-
info_seekeR almost 5 yearsI am slightly uneasy with that Just hoping somebody has a different solution. If not, then I will have to accept your suggestion (and lose hope ;) )
-
Rory almost 5 yearsYou can program it. What you can't do is make a pivot table not group its fields; it just doesn't make sense.
-
info_seekeR almost 5 years@Rory what you say makes sense. Feel free to add your comment as an answer so that I can accept.
-
-
info_seekeR almost 5 yearsThanks for your contribution! Unfortunately the linked article only talks about changing the layout, whereas I want to change the calculations too (hence I don't need a nested group of gender within age to form).
-
info_seekeR almost 5 yearsUnfortunately it still did not address my need. As @Rory put it, I really need separate pivot tables, but without the hassle of making them manually (I have a lot of rows to group by)