Pivot table custom grouping of rows ?

Question

Hello everyone...

I am trying to present some quarterly sales data by category and key customer. Each category (convenience, grocery, department) is mostly comprised of a few large, key customers. Is there a way that I can custom group a block of customers into an "All Other" within each category of a pivot table? I've tried to highlight and group within a category but it applies that grouping to all categories within my pivot table.

For example, the convenience category only has 3 large, relevant customers and the rest I want to put into an "All Other Convenience" group. However, the grocery category has 4 large customers, the rest I want to put into an "All Other Grocery" group.

I appreciate any insight you could provide.

ck

Answer 1

Hello everyone...

I am trying to present some quarterly sales data by category and key customer. Each category (convenience, grocery, department) is mostly comprised of a few large, key customers. Is there a way that I can custom group a block of customers into an "All Other" within each category of a pivot table? I've tried to highlight and group within a category but it applies that grouping to all categories within my pivot table.

For example, the convenience category only has 3 large, relevant customers and the rest I want to put into an "All Other Convenience" group. However, the grocery category has 4 large customers, the rest I want to put into an "All Other Grocery" group.

I appreciate any insight you could provide.

ck

For this part only:-

For example, the convenience category only has 3 large, relevant customers and the rest I want to put into an "All Other Convenience" group.

 - try this:-

1.

Click on:-

Starvin Marvin

 - and keeping the CTRL button down

 - also click on:-

Plaid Pantry

 - so that both items are highlighted then:-

PivotTable Analyze tab

Group group

Click on:-

Group Selection

You should now see:-

Starvin Marvin

 - and:-

Plaid Pantry

 - beneath:-

Group1

2.

Click on:-

Group1

 - then:-

PivotTable Analyze tab

PivotTable group

Click on the drop down arrow to the right of:-

Options

 - then click on:-

Options

 - to open the window called:-

PivotTable Options

3.

In the window called:-

PivotTable Options

 - change the field called:-

PivotTable Name:

 - to read:-

All Other Convenience

 - then in the lower right-hand corner of the same window click on:-

OK

4.

Now click on:-

Group1

 - and change it to read:-

All Other Convenience

Does that give you what you want?

Repeat the above for other groupings as required.

Answer 2

Hi,

What is the basis of deciding the customers that will fall in the "All Other" category?

Answer 3

 

. . .

 

2.

 

Click on:-

 

Group1

 

 - then:-

 

PivotTable Analyze tab

 

Active Field group

 

Click:-

 

Field Settings

 

 - to open the window called:-

 

Field Settings

 

3.

 

In the window called:-

 

Field Settings

 

 - change the field called:-

 

Custom Name:

 

 - to read:-

 

All Other Convenience

 

 - then in the lower right-hand corner of the same window click on:-

 

OK . . .

 

Hi @trip_to_tokyo,

I found this question interesting for My learning.

I read Your answer . . I could not understand the reason for Step 2 . . please explain what is Step 2 intended to do?

Thank You!

Answer 4

Apologies my steps 2. and 3. in my previous posting were incorrect. Complete amended procedure is:-

For this part only:-

For example, the convenience category only has 3 large, relevant customers and the rest I want to put into an "All Other Convenience" group.

 - try this:-

1.

Click on:-

Starvin Marvin

 - and keeping the CTRL button down

 - also click on:-

Plaid Pantry

 - so that both items are highlighted then:-

PivotTable Analyze tab

Group group

Click on:-

Group Selection

You should now see:-

Starvin Marvin

 - and:-

Plaid Pantry

 - beneath:-

Group1

2.

Click on:-

Group1

 - then:-

PivotTable Analyze tab

Active Field group

Click:-

Field Settings

 - to open the window called:-

Field Settings

3.

In the window called:-

Field Settings

 - change the field called:-

Custom Name:

 - to read:-

All Other Convenience

 - then in the lower right-hand corner of the same window click on:-

OK

4.

Now click on:-

Group1

 - and change it to read:-

All Other Convenience

Does that give you what you want?

Repeat the above for other groupings as required.

Answer 5

In answer to this question:-

I could not understand the reason for Step 2 . . please explain what is Step 2 intended to do?

Steps 2. and 3. in my amended procedure (my second posting) need to be read in conjunction with each other.

Once step 3.’s been completed on the right hand side of the EXCEL window in the Task Pane called:-

PivotTable Fields

 - you’ll see a new item in the lower part of that Task Pane called:-

All Other Convenience

As you progress through my amended second procedure once you’ve completed each step (but before you move on to the next one) examine the impact of what’s happening in the Task Pane called:-

PivotTable Fields

Doing this will give you a better comprehension of what’s happening and why.