SOLVED

Pivot table grouping

%3CLINGO-SUB%20id%3D%22lingo-sub-2066906%22%20slang%3D%22en-US%22%3EPivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066906%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20when%20im%20grouping%20age%20in%20a%20pivot%20table%20by%2010%2C%20it%20groups%20as%200-10%2C%2010-20%20instead%20of%200-9%2C%2010-19%20.%20Anyone%20know%20how%20to%20fix%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2066906%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067026%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931467%22%20target%3D%22_blank%22%3E%40muna950%3C%2FA%3E%26nbsp%3BDid%20this%20on%20a%20Mac.%20Should%20work%20similar%20on%20a%20Windows%20PC.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-01-16%20at%2014.20.00.png%22%20style%3D%22width%3A%20440px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246926iF44EE1602F0F6D74%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-01-16%20at%2014.20.00.png%22%20alt%3D%22Screenshot%202021-01-16%20at%2014.20.00.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067028%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931467%22%20target%3D%22_blank%22%3E%40muna950%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20groups%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20371px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246927i9E07AF1133A4FB3C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067035%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067035%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20but%20the%20same%20process%20for%20me%20groups%20them%20into%200-10%2C%2010-20%20instead%20of%200-9%2C%2010-19%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-01-16%20at%204.38.14%20PM.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246929i10506D2C0D66EF02%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-01-16%20at%204.38.14%20PM.png%22%20alt%3D%22Screen%20Shot%202021-01-16%20at%204.38.14%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-01-16%20at%204.38.23%20PM.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246928iA8F2B39D31236465%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-01-16%20at%204.38.23%20PM.png%22%20alt%3D%22Screen%20Shot%202021-01-16%20at%204.38.23%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067036%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067036%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%20but%20the%20grouping%20is%20not%20the%20same%20for%20me.%20Kindly%20look%20at%20my%20other%20reply.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067063%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931467%22%20target%3D%22_blank%22%3E%40muna950%3C%2FA%3E%26nbsp%3BGet%20the%20same%20result%20when%20the%20numbers%20to%20be%20grouped%20contain%20decimals.%20Don't%20really%20know%20why%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067065%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067065%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931467%22%20target%3D%22_blank%22%3E%40muna950%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOh%2C%20yes%2C%20if%20numbers%20have%20decimals%20PivotTable%20groups%20by%20such%20way.%20With%20that%3C%2FP%3E%0A%3CP%3E0-10%20means%20from%20zero%20and%20less%20than%2010%3C%2FP%3E%0A%3CP%3E10-20%20means%20more%20or%20equal%20to%2010%20and%20less%20than%2020%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20remember%20I've%20seen%20workaround%20for%20that.%20On%20the%20other%20hand%20that's%20logical.%20If%2C%20for%20example%2C%20you%20have%209.5%20and%20groups%200-9%3B%2010-19%20in%20which%20group%20will%20be%209.5%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2067251%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067251%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BYes%2C%20that%20makes%20a%20lot%20of%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello. when im grouping age in a pivot table by 10, it groups as 0-10, 10-20 instead of 0-9, 10-19 . Anyone know how to fix this?

9 Replies

@muna950 Did this on a Mac. Should work similar on a Windows PC. Example attached.

Screenshot 2021-01-16 at 14.20.00.png

@muna950 

It groups as

image.png

Thank you but the same process for me groups them into 0-10, 10-20 instead of 0-9, 10-19 Screen Shot 2021-01-16 at 4.38.14 PM.pngScreen Shot 2021-01-16 at 4.38.23 PM.png@Riny_van_Eekelen 

Thank you @Sergei Baklan , but the grouping is not the same for me. Kindly look at my other reply.

@muna950 Get the same result when the numbers to be grouped contain decimals. Don't really know why? 

Best Response confirmed by muna950 (Occasional Contributor)
Solution

@muna950 

Oh, yes, if numbers have decimals PivotTable groups by such way. With that

0-10 means from zero and less than 10

10-20 means more or equal to 10 and less than 20

 

I don't remember I've seen workaround for that. On the other hand that's logical. If, for example, you have 9.5 and groups 0-9; 10-19 in which group will be 9.5?

Thank you so much @Sergei Baklan Yes, that makes a lot of sense.

Thank you @Riny_van_Eekelen . Someone explained that it is due to the decimal points as well so i may have to consider rounding them off.

@muna950 , glad to help