Preventing nested grouping when adding rows to pivot table in Excel
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?
excel pivot-table
asked Nov 15 '18 at 11:29
info_seekeRinfo_seekeR
724821
|
show 1 more comment
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?
excel pivot-table
asked Nov 15 '18 at 11:29
info_seekeRinfo_seekeR
724821
1
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.
– Rory
Nov 15 '18 at 11:54
Indeed, separate. But that calls for a lot of manual work, and I was hoping there is some way to automatically achieve it
– info_seekeR
Nov 15 '18 at 11:56
Copy/paste the pivot and swap the row field.
– Rory
Nov 15 '18 at 11:58
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 ;) )
– info_seekeR
Nov 15 '18 at 12:03
1
You can program it. What you can't do is make a pivot table not group its fields; it just doesn't make sense.
– Rory
Nov 15 '18 at 12:06
|
show 1 more comment
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?
excel pivot-table
asked Nov 15 '18 at 11:29
info_seekeRinfo_seekeR
724821
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?
excel pivot-table
excel pivot-table
asked Nov 15 '18 at 11:29
info_seekeRinfo_seekeR
724821
asked Nov 15 '18 at 11:29
info_seekeRinfo_seekeR
724821
asked Nov 15 '18 at 11:29
info_seekeRinfo_seekeR
724821
asked Nov 15 '18 at 11:29
info_seekeRinfo_seekeR
724821
asked Nov 15 '18 at 11:29
info_seekeRinfo_seekeR
724821
724821
1
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.
– Rory
Nov 15 '18 at 11:54
Indeed, separate. But that calls for a lot of manual work, and I was hoping there is some way to automatically achieve it
– info_seekeR
Nov 15 '18 at 11:56
Copy/paste the pivot and swap the row field.
– Rory
Nov 15 '18 at 11:58
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 ;) )
– info_seekeR
Nov 15 '18 at 12:03
1
You can program it. What you can't do is make a pivot table not group its fields; it just doesn't make sense.
– Rory
Nov 15 '18 at 12:06
|
show 1 more comment
1
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.
– Rory
Nov 15 '18 at 11:54
Indeed, separate. But that calls for a lot of manual work, and I was hoping there is some way to automatically achieve it
– info_seekeR
Nov 15 '18 at 11:56
Copy/paste the pivot and swap the row field.
– Rory
Nov 15 '18 at 11:58
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 ;) )
– info_seekeR
Nov 15 '18 at 12:03
1
You can program it. What you can't do is make a pivot table not group its fields; it just doesn't make sense.
– Rory
Nov 15 '18 at 12:06
1
1
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.
– Rory
Nov 15 '18 at 11:54
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.
– Rory
Nov 15 '18 at 11:54
Indeed, separate. But that calls for a lot of manual work, and I was hoping there is some way to automatically achieve it
– info_seekeR
Nov 15 '18 at 11:56
Indeed, separate. But that calls for a lot of manual work, and I was hoping there is some way to automatically achieve it
– info_seekeR
Nov 15 '18 at 11:56
Copy/paste the pivot and swap the row field.
– Rory
Nov 15 '18 at 11:58
Copy/paste the pivot and swap the row field.
– Rory
Nov 15 '18 at 11:58
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 ;) )
– info_seekeR
Nov 15 '18 at 12:03
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 ;) )
– info_seekeR
Nov 15 '18 at 12:03
1
1
You can program it. What you can't do is make a pivot table not group its fields; it just doesn't make sense.
– Rory
Nov 15 '18 at 12:06
You can program it. What you can't do is make a pivot table not group its fields; it just doesn't make sense.
– Rory
Nov 15 '18 at 12:06
|
show 1 more comment
2 Answers
2
active
oldest
votes
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.
answered Nov 15 '18 at 12:16
RoryRory
24.3k51723
add a comment |
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:
answered Nov 15 '18 at 11:37
ygaftygaft
15717
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
Nov 15 '18 at 11:42
@info_seekeR check out my updated answer
– ygaft
Nov 15 '18 at 11:51
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)
– info_seekeR
Nov 15 '18 at 11:57
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53318477%2fpreventing-nested-grouping-when-adding-rows-to-pivot-table-in-excel%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 15 '18 at 12:16
RoryRory
24.3k51723
add a comment |
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.
answered Nov 15 '18 at 12:16
RoryRory
24.3k51723
add a comment |
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.
answered Nov 15 '18 at 12:16
RoryRory
24.3k51723
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.
answered Nov 15 '18 at 12:16
RoryRory
24.3k51723
answered Nov 15 '18 at 12:16
RoryRory
24.3k51723
answered Nov 15 '18 at 12:16
RoryRory
24.3k51723
answered Nov 15 '18 at 12:16
RoryRory
24.3k51723
24.3k51723
add a comment |
add a comment |
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:
answered Nov 15 '18 at 11:37
ygaftygaft
15717
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
Nov 15 '18 at 11:42
@info_seekeR check out my updated answer
– ygaft
Nov 15 '18 at 11:51
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)
– info_seekeR
Nov 15 '18 at 11:57
add a comment |
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:
answered Nov 15 '18 at 11:37
ygaftygaft
15717
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
Nov 15 '18 at 11:42
@info_seekeR check out my updated answer
– ygaft
Nov 15 '18 at 11:51
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)
– info_seekeR
Nov 15 '18 at 11:57
add a comment |
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:
answered Nov 15 '18 at 11:37
ygaftygaft
15717
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:
answered Nov 15 '18 at 11:37
ygaftygaft
15717
edited Nov 15 '18 at 11:50
answered Nov 15 '18 at 11:37
ygaftygaft
15717
answered Nov 15 '18 at 11:37
ygaftygaft
15717
answered Nov 15 '18 at 11:37
ygaftygaft
15717
15717
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
Nov 15 '18 at 11:42
@info_seekeR check out my updated answer
– ygaft
Nov 15 '18 at 11:51
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)
– info_seekeR
Nov 15 '18 at 11:57
add a comment |
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
Nov 15 '18 at 11:42
@info_seekeR check out my updated answer
– ygaft
Nov 15 '18 at 11:51
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)
– info_seekeR
Nov 15 '18 at 11:57
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
Nov 15 '18 at 11:42
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
Nov 15 '18 at 11:42
@info_seekeR check out my updated answer
– ygaft
Nov 15 '18 at 11:51
@info_seekeR check out my updated answer
– ygaft
Nov 15 '18 at 11:51
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)
– info_seekeR
Nov 15 '18 at 11:57
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)
– info_seekeR
Nov 15 '18 at 11:57
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53318477%2fpreventing-nested-grouping-when-adding-rows-to-pivot-table-in-excel%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
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.
– Rory
Nov 15 '18 at 11:54
Indeed, separate. But that calls for a lot of manual work, and I was hoping there is some way to automatically achieve it
– info_seekeR
Nov 15 '18 at 11:56
Copy/paste the pivot and swap the row field.
– Rory
Nov 15 '18 at 11:58
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 ;) )
– info_seekeR
Nov 15 '18 at 12:03
1
You can program it. What you can't do is make a pivot table not group its fields; it just doesn't make sense.
– Rory
Nov 15 '18 at 12:06