Pivot Table Custom Grouping: With 3 Criteria

In this article, we will discuss the Custom Grouping in Pivot table with vivid illustrations. We will use the below data set to explain Custom Grouping in Pivot Table.

What is the Pivot Table in Excel?

Pivot table is one of the statistical tools of Microsoft Excel. We use it to work with a large amount of data and need to represent and summarize data in desired ways. By using the Pivot table, we can easily extract the desired data from a giant data set.

In the Pivot table, we can rotate the data in the table to view from a different perspective. You don’t have to apply any further formula or other shortcuts to change the orientation of the data you want to show.

What Does Pivot Table Grouping Mean?

Grouping means organizing some data based on the same criteria. Like, all numerical data, and text data. The Pivot table also has a grouping facility. In the Pivot table, we can group data according to our needs. There is no restriction in grouping data in Excel.

How to Create a Pivot Table and Apply Grouping?

In this section, we will show how to create a pivot table and apply the group in the Pivot table. Additionally, we also show how to undo grouping.

Step 1:

• First, go to the Insert tab.
• Choose From Table/Range from the Pivot Table tool.

Step 2:

• Now, put the range that we want to convert in the Pivot table on the Table/Range box.
• Then, put the cell reference of the worksheet on the Location box.

Step 3:

• Finally, press OK.

Now, we get the PivotTable Field options. From this field we will customize the Pivot table/

Step 4:

• Now, select Sales Person and Bill from the field.

Now, we get the table of bills in terms of each salesperson.

Step 5:

• We can also change the data presentation. Select Customer Name instead of the Sales Person and see the changes on the below image.

Here, bills are showing in terms of the Customer Name.

Now, we will apply the grouping on the Pivot table.

Step 6:

• Select three names that start with “J”, those will be in a group.
• Go to the PivotTable Analyze tab.
• Then, select the Group Selection option.

Now, we can see that those cells formed a group named Group1.

We can also apply this grouping in another way.

Step 7:

• Select the rest two cells first.
• Then press the Right button of the mouse.
• From the newly appearing menu select the Group option.

Now, we see that another group is created named Group2.

We can form a group of two-way ribbon options or mouse shortcuts. People can use any of those options they want.

Undo Grouping from Pivot Table

We will disclose how to undo grouping or ungroup in the Pivot table. We can do these two ways.

Method 1:

• First, select any of the groups. Here, we select Group1.
• Then, press the Right button of the mouse. A menu will appear.
• Choose Ungroup from that menu.

Now, we can see that Group1 is removed in the below image.

Method 2:

• First, select Group2.
• Then go to the PivotTable Analyze tab.
• Choose Ungroup option.

Now, see that Group 2 is not showing anymore.

Pivot Table for Custom Grouping: 3 Types of Object

In this section, we will discuss how to customize grouping in the Pivot table.

1. Grouping Dates in Pivot Table

Grouping of dates in the Pivot table is quite interesting. We don’t need to group manually. This grouping is done automatically. We will apply grouping on the below data set, which is formed by data and bill segments.

1.1 Group Dates Based on Years in Pivot Table

In this section, we will group based on years.

Step 1:

• First, select any of the cells on the Pivot table.
• After that click on the Right button of the mouse and choose Group from the menu.

Step 2:

• A dialog box will appear. Tick the boxes of Starting at and Ending at.
• Then, choose Years.

Step 3:

• Finally, press OK and see the return.

Here, the Sum of Bill is shown in terms of years only.

1.2 Group Dates Based on Quarters in Pivot Table

In this section, we will group based on quarters.

Step 1:

• First, select any of the cells on the Pivot table.
• After that click on the Right button of the mouse and choose Group from the menu.

Step 2:

• A dialog box will appear. Tick the boxes of Starting at and Ending at.
• Then, choose Quarters.

Step 3:

• Finally, press OK and see the return.

Here, the Sum of Bill is shown in terms of quarters only.

But one problem arises, we cannot identify the quarters with corresponding years. Now, we will solve this problem by the below method.

Step 4:

• Select both the Quarters and Years from the box.

Step 5:

• Again, press OK to see what happens.

Now, we can easily say that which quarter from which year.

1.3 Group Dates Based on Months in Pivot Table

In this section, we will group based on months.

Step 1:

• First, select any of the cells on the Pivot table.
• After that click on the Right button of the mouse and choose Group from the menu.

Step 2:

• A dialog box will appear. Tick the boxes of Starting at and Ending at.
• Then, choose Months.

Step 3:

• Then, press OK and see the return.

Here, the Sum of Bill is shown in terms of months only.

But from that, we can specify the bill of a specific month with a specific year. We will apply a method to solve this issue.

Step 4:

• Select both the Months and Years from the box.

Step 5:

• Again, press OK to see the return.

Now, we can easily specify the sum of the bill of any month with a specific year.

1.4 Group Dates Based on Day in Pivot Table

In this section, we will group based on days.

Step 1:

• First, select any of the cells on the Pivot table.
• After that click on the Right button of the mouse and choose Group from the menu.

Step 2:

• A dialog box will appear. Tick the boxes of Starting at and Ending at.
• Then, choose Days.
• On the Number of days box put 7.

Step 3:

• Then, press OK and see the return.

Here, we set the number of days 7 as we want to get the sum for each week. We can also modify this day value and put whatever interval need.

Read more: How to Group Rows in Excel Pivot Table

2. Group Text Items in Pivot Table

We will show the grouping of text items in the Pivot Table.

The above Pivot table will consider for this segment. We will group based on text items of this Pivot Table.

2.1 Create Text-based Group in Pivot Table:

Here, we will show how to form a text-based group in the Pivot table.

• First, select Chicago and New York, as both form a group.
• Then, click the Right button of the mouse.
• From Group from the menu.

From the below image we can see that a new group is constructed naming Group1.

In the same way, create Group 2 that consists of Dallas and Los Angeles.

2.2 Rename Group in Pivot Table:

We can easily rename any Group in Excel Pivot Table.

Step 1:

• First, select any of the groups. Here, we select Group1.
• Then press F2.
• Now, put a new name Old_Store.

Step 2:

• Now, press Enter and see what happens.

In the same way, rename Group2 as New_Store.

2.3 Change Cell Format in Pivot Table:

The pivot table has the cell format option too.

Step 1:

• Select any of the cells from the value field.
• Press the right button of the mouse.
• Choose the Value Field Settings.

Step 2:

• We get Number Format from the new field. Select this Number Format.

Now, get the Format Cells field.

We already know how to change the cell format.

2.4 Some Additional Features of Grouping In Pivot Table:

In this section, we will discuss some additional features of Grouping in the Pivot table.

We can change the field operation.

• Go to the Value Field Settings as shown earlier.

• Then from the Summarize value field by box choose other operations like Max.

• Finally, press OK and see what occurs.

We can also present the value in terms of percentage.

• Select Show Values As from the Value Field Settings.

• Then press OK.

The sum of the bill is represented in percentage.

We can also represent a value in terms of multiple variables.

Here, in the first step values are shown according to the Store, then based on years.

We can also get the details of any specific cell.

• Press the Right button of the mouse.
• Then select Show Details from the menu.

Now, we see the details of that cell.

Read more: How to Group Columns in Excel Pivot Table

3. Group Numeric Values in Excel

In this section, we will discuss how to group numeric values in Pivot tables.

Step 1:

• Here, we customize a Pivot in terms of bills and customers number.

Step 2:

• Now, select any of the cells of the bills.
• Then press the Right button of the mouse.
• A new menu will appear. Select Group from that menu.

Step 3:

• A dialog box will appear. Put our customized values on the boxes.

Step 4:

• Finally, press OK.

Here, the number of customers is shown in terms of bill amounts.

Read More: How to Group Numbers in Excel Pivot Table

How to Turn Off Auto Grouping of Date in Excel Pivot Table

We can manually turn off the auto grouping of dates in the Excel Pivot table. This option is available from Excel 2016 to later versions.

Step 1:

• First, select File from the main tab.

Step 2:

• Then go to Option.

Step 3:

• First, go to the Data tab.
• Then we will see Disable automatic grouping of Date/Time columns in the PivotTables. This option will turn off the auto grouping of date and time.

Note:

This setting change will affect all your Excel workbook files.

Read more: [Fixed] Excel Pivot Table: Cannot Group That Selection

Things to Remember

When we apply custom grouping on the pivot table need to remember the following things.

• If you want to group based on hours, minutes, seconds. That is also possible if the data set contains time arguments.
• When we form a Pivot table, we should be careful about the different PivotTable fields.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we discussed custom grouping in the Pivot table. I hope this will satisfy your needs. Please, give your suggestions in the comment box.

Further Readings

• How to Rename a Default Group Name in Pivot Table
• How to Make Group by Same Interval in Excel Pivot Table
• How to Use Excel Pivot Table to Group by Different Intervals

<< Go Back to Group Pivot Table | Pivot Table in Excel | Learn Excel