Grouping in a pivot

Occasional Contributor

Hello, I would like to group the data into ranges but when I follow the instruction I found on internet for grouping what happens is that all the values are grouped into a single group (as you can see in the screen). How can I select different ranges?

 

Thank you!

FranciPisa_0-1657791094642.png

 

9 Replies

@FranciPisa 

One way would be to define the groups in an additional column in the source data.

But you can also specify the groups in the pivot table itself:

 

First, ungroup the row labels.

Then select the values you want in the first group.

Right-click in the selection and select Group... from the context menu.

All values outside the selection will be in a group of their own.

Select the ones you want to be in the second group, right-click and select Group...

Repeat as long as needed.

Hello Hans,

thank you for the reply what I would like to have is to group the data into ranges. I have found on internet that if you right click on a cell then you should have a context menu and you should be able to define the range of values.

However, when I follow the process I do not get the context menu and if I select all the values and I click group what I obtain is what you can see in the screenshot

@FranciPisa 

Have you tried selecting a single cell in he Row Labels area of the pivot table?

You should get a context menu with Group... in it.

S1570.png

Clicking Group... results in a dialog:

S1571.png

If you don't see the context menu: which version of Excel do you use? The desktop version for Windows or Mac, or the online (browser) version, or the iOS/Android version?

@Hans Vogelaar 

Yes I did select a cell in the row label column but when I click it I get this message 

FranciPisa_0-1657797921053.png

FranciPisa_1-1657797972406.png

I have no idea about how to solve it

@FranciPisa 

Do your row labels contain text values? Excel cannot group text values.

@Hans Vogelaar 

they are number as you can see from my screenshot below.

FranciPisa_0-1657800104016.png

 

@FranciPisa 

What is the "-"? That doesn't look like a number...

@Hans Vogelaar 

Yes I had to delete it manually (I was just filtering it out).

Can I ask you another question regarding the grouping?

How can I manage to do the subgroups like the screenshot? I managed to do the normal ones while I cannot do the bold ones..

Thanks!

FranciPisa_0-1657805263051.png

 

@FranciPisa 

The only way I can think of is to use additional columns in the source data. Let's see if someone else comes up with a better idea.