Group Data in Pivot Table – Dates, Numbers, Text

Grouping the Data items in the pivot table help us quickly reach conclusions without going into a deep study. We can define ranges for numbers, Quarters, or Months for Dates and Manually Group the text values to make groups of data in the pivot table and read them combined.

Let us see how we can achieve it!

Grouping Dates (Data) by Months and Quarters in Pivot Table

We have learned to make a Pivot Table in Excel in our previous blog.

So in this example, let us say we have the orders summary placed on different days of a month as follows:-

example for grouping the dates in pivot tables

We have got the Dates and then the number of items sold on that date of the year. It is easy to read the items sold on each date but what if I want to check up on the total sales for January?

The answer is simple. We can just group the dates ( data) into months in the pivot table. To group the dates:-

  • Right-click on any one of the dates in the pivot table.
  • Choose the option Group from the shortcut menu.
example for grouping the dates in pivot tables step 2
  • Mark the Option for Months in the Grouping Dialog Box and click Ok.
example for grouping the dates in pivot tables step 3

This gives us the pivot table as follows:-

example for grouping the dates in pivot tables step 4

And from the pivot table, we come to know that the total sales were maximum in the month of September (147) and minimum in March (39).

We can even do nested grouping. In other words, groups combined to make some more groups.

What if we group these months (group of dates) into Quarters (group of months). All we need to do is, select both months and quarters in step 3.

example for grouping the dates in pivot tables step 5

You can press the minus button before the Quarter names to hide the contents and this can be toggled.

example for grouping the dates in pivot tables step 6
infographics group data in pivot tables

Grouping the Weights into Ranges

Let us say we have generated a pivot table that has the weights and the corresponding number of class students with that weight.

grouping the numbers into ranges in pivot tables in excel step 1

Now we have got the number of students corresponding to the value of the weight but we would now form ranges for these weights. To do so:-

  • Right-click on any weight value and choose the option Group.
  • Set the starting (41) and ending (80) along with the dividing factor (10)
grouping the numbers into ranges in pivot tables in excel step 2

This forms the ranges with a width of 10 kgs that start from 41 kgs and end at 80 kgs as follows:-

grouping the numbers into ranges in pivot tables in excel step 3

So you can see most of the students have their weights between 41-50 kgs.

Grouping the Texts

Let us say we have the sales of different vegetables and fruits of a shop as follows:-

groping the text in pivot table in excel

Now we want to make two groups of vegetables and fruits each. To do so:-

  • Hold the Ctrl key and choose Kiwi, Apples, Banana and Mango.
groping the text in pivot table in excel step 1
  • Right, CLick and then choose the option for Group.
  • Group1 will be formed, rename it to Fruits from the formula bar.
change the name of group in pivot table in excel
  • Select the remaining items i.e onion, potato, and tomato.
groping the text in pivot table in excel step 2
  • Group them from the shortcut menu and rename group2 as vegetables.
groping the text in pivot table in excel step 3

We hope that you have learned how to Group Data in Pivot Tables.

This brings us to the end of the blog.

Thank you for reading.

Leave a Comment