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:

https://support.office.com/en-us/article/set-pivottable-default-layout-options-efd8569c-f07a-43c1-9db2-4f2912a0f94e

You need to change both of these settings: enter image description here

Share:
17,360

Related videos on Youtube

info_seekeR
Author by

info_seekeR

https://www.linkedin.com/in/aliarsalankazmi

Updated on June 04, 2022

Comments

  • info_seekeR
    info_seekeR over 1 year

    enter image description here

    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 the row category of pivot 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
      Rory almost 5 years
      That'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
      info_seekeR almost 5 years
      Indeed, separate. But that calls for a lot of manual work, and I was hoping there is some way to automatically achieve it
    • Rory
      Rory almost 5 years
      Copy/paste the pivot and swap the row field.
    • info_seekeR
      info_seekeR almost 5 years
      I 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
      Rory almost 5 years
      You 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
      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
    info_seekeR almost 5 years
    Thanks 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
    info_seekeR almost 5 years
    Unfortunately 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)