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.

Data set of Pivot Table Custom Grouping


Download Practice Workbook

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


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, 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.

Create a Pivot Table and Apply Grouping

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.

Create a Pivot Table and Apply Grouping

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.

Create a Pivot Table and Apply Grouping

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.

Create a Pivot Table and Apply Grouping

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.

Create a Pivot Table and Apply Grouping

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

Create a Pivot Table and Apply Grouping

We can form a group of two ways 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.

Undo Grouping From Pivot Table:

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.

Undo Grouping From Pivot Table:

Now, see that Group 2 is not showing anymore.


3 Types of Object in Pivot Table for Custom Grouping

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 segment.

Grouping Dates in Pivot Table


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.

Group Dates based on Years in Pivot Table

Step 3:

  • Finally, press OK and see the return.

Group Dates based on Years in Pivot Table

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.

Group Dates based on Quarters in Pivot Table

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.

Group Dates based on Quarters in Pivot Table

Step 5:

  • Again, press OK to see what happens.

Group Dates based on Quarters in Pivot Table

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.

Group Dates based on Months in Pivot Table

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.

Group Dates based on Months in Pivot Table

Step 5:

  • Again, press OK to see the return.

Group Dates based on Months in Pivot Table

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.

Group Dates based on Day in Pivot Table

Step 3:

  • Then, press OK and see the return.

Group Dates based on Day in Pivot Table

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: Excel Pivot Table Group by Week


Similar Readings


2. Group Text Items in Pivot Table

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

Group Text Items in 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.

Create Text-based Group in Pivot Table

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.

Create Text-based Group in Pivot Table


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.

Rename Group in Pivot Table

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.

Change Cell Format in Pivot Table

Step 2:

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

Change Cell Format in Pivot Table

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.

Additional features of Grouping In Pivot Table

  • 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.

Additional features of Grouping In Pivot Table

  • Then press OK.

The sum of the bill is represented in percentage.

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

Additional features of Grouping In Pivot Table

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.

Additional features of Grouping In Pivot Table

Now, we see the details of that cell.

Read more: How to Remove Format As Table in Excel


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.

Group Numeric Values in Excel

Step 4:

  • Finally, press OK.

Group Numeric Values in Excel

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

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.

Turn Off Auto Grouping of Date in Excel Pivot Table

Note:

This setting change will affect all your Excel workbook files.

Read more: [Fix] Cannot Group Dates in Pivot Table


Things to Remember

When we will apply custom grouping on the pivot table need to remember the below 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.

Conclusion

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


Further Readings

Tags:

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo