How to Group Data in Pivot Table (3 Different Examples)

Get FREE Advanced Excel Exercises with Solutions!

It is difficult to deal with a large dataset, right? But if you can convert the dataset into some groups (based on data type) then it will be easier. And for handling these data, the more convenient way is to create a pivot table. So, today, in this article, I will explain how to group data in a pivot table in Excel.

Below, I have attached a basic overview image of my article.

How to Group Data in Pivot Table

Also, I will show here some other queries about grouping data in pivot tables.

For conducting the session, I will use the Microsoft 365 version.


How to Group Data in Pivot Table: 3 Practical Examples

Here, I will use a simple dataset about the sales of a company for your better understanding. First of all, I have to make the pivot table.

Always try to avoid creating the pivot table directly from the data. You should make the pivot table from the Excel table. Because, in this case, you can update your pivot table.

So, let’s start with making the Excel table.

Making an Excel Table from data

  • Select any cell from the dataset >> go to Insert tab >> click on Table.

After pressing Table, you will get a dialog box named “Create Table“. In that dialog box, you will get the “Where is the data for your table?” box is auto filled. Also, “My table has headers” is checked.

  • Now, press OK on the “Create Table” dialog box.

So, you convert the data into an Excel table.

Creating Pivot Table from Excel table

  • Again, select any cell with the table >> then go to the Insert tab >> from PivotTable >> click on From Table/Range.

After clicking on From Table/Range, you will see a new dialog box named “PivotTable from table or range”. Where you will find, the “Table/Range” box is filled.

  • Now, choose where you want to set the pivot table. Here, I have selected “New Worksheet” >> then press OK.

After that, you will get a blank pivot table in a new sheet.

A blank Pivot table

Now, let’s see the following Examples of how to group data in a pivot table.


1. Defining a New Group with Some Certain Text Items: Custom Group in Pivot Table

Here, I have to drag preferable fields into areas to introduce data to the blank pivot table. To make the pivot table, I drag the Item field in the Rows area, and the Units field in the Values area. You will see the Units as the sum of units.

In this section, I will make a group with some selected text items. Also, I will show you how to rename that group.

Defining a New Group with Some Certain Text Items

  • Now, it’s time to set which items should be in one group. I want to make a group with Notebook, Pen, and Pencil.
  • So, press the CTRL key >> select these items >> right-click on Mouse >> from the Context Menu Bar >> choose Group.

As a result, you will see there is Group1. Under which there are Notebook, Pen, and Pencil. Notice that, in the Rows area the items of Group1 are named Item2.

Let’s change this built-in name.

Going to Field Settings from Pivot Table Analyze

  • So, select the Group1 >> as a result you will get a new tab named PivotTable Analyze >> go to PivotTable Analyze >> from the Active Field >> select Field Settings.

So, the Field Settings dialog box will appear.

Setting Custom Name to Field Settings dialog box

  • Now, in that dialog box >> write a name in the Custom Name box (here, I have written School-Items) >> press OK.

As a result, you will see the name of the field of Group1 as School-Items. But to change the name of Group1, you have to use Formula Bar.

Change the Custom Name for Group1

  • Select Group1 >> write a preferable name in the Formula Bar.

Change the Display name of Group1

  • Similarly, I have created another group with other items and named that group Others.

Make another Group named Others


2. Grouping Numbers with Pivot Table in Excel

In this example, I will make a group with a field having numbers. Here, I have made the pivot table by dragging Region and Total Sales in the Rows area, and Total Sales in the Values area.

Create a Pivot table with Region and Total Sales

  • Now, click on A5 cell >> from the PivotTable Analyze tab >> go to Group >> choose Group Selection.

After pressing “Group Selection“, you will get the “Grouping” dialog box.

  • In that dialog box, define Starting at (0), Ending at (300), By (25) and then press OK.

Here, the Starting at denotes from which value the group should be started. The Ending at means up to which value the group should be continued. The value in the By box defines the class interval of the group.

How to Group Numbers with Pivot Table in Excel

Here, you can see the created group below. Now, I want to check in that sale range how many sales are presented.

  • So, click on the drop-down arrow of Sum of Total Sales >> then choose Value Field Settings.

Going to Value Field Settings

As a result, another dialog box named “Value Field Settings” will appear.

  • From the Summarize value field by menu >> select Count >> press OK.

Summarizing value field by Count

Finally, you will see how many sales are conducted in these sales ranges.

Creating Group based on number data in Pivot Table


Make a Visual Representation of Grouping Numbers

You can make a visual representation of your grouped data using the PivotChart option.

  • To do so, click on any cell to activate the PivotTable Analyze tab >> from the PivotTable Analyze tab >> go to Tools option >> select PivotChart.

Inserting Pivot Chart

So, you will get the “Insert Chart” dialog box.

  • The Clustered Column chart will be auto-selected>> press OK on that.

Inserting Clustered Column Chart

  • Now right-click on data series (clustered column) >> from the Context Menu Bar >> choose Format Data Series.

Working with pivot chart

So, you will see the Format Data Series at the right-most corner of the Excel sheet.

  • Now, decrease the Gap Width.

Decreasing Gap Width of the Pivot Chart

Lastly, you will get the visual version of your grouped data. Here, I have changed the title name of the chart to Sales Review.

Making a Visual Representation of Grouping Numbers


3. Grouping Date with Pivot Table in Excel

Now, I will group the pivot table based on Date values. Excel creates a group of Date values in the pivot table by itself. So, let’s see what Excel creates for you and how we can create a manual one.


3.1 Built-in Date Grouping

In my dataset, there is a field named Order Date having date values. Now, drag this Order Date to the Rows area and see the result. Excel creates these auto groups (Years, Quarters, Months).

Firstly, Excel divides the date field into years. Then divides them into quarters, and lastly divides them into months.

Under months there will be the individual dates, if you want to remove the exact date then drag the Order Date from the Rows area to the back.

Excel creates auto group with Date type data


3.2 Manually Grouping of Date Type Data in Pivot Table

Now, let’s create a manual one. Dragging the date field creates the auto group. So, first of all, you have to ungroup them.

  • Select any value from the grouped data >> go to the PivotTable Analyze tab >> from the Group >> choose Ungroup.

Ungrouping data

  • Now, click on A4 cell >> from the PivotTable Analyze tab >> go to Group >> choose Group Selection.

After pressing “Group Selection“, you will get the “Grouping” dialog box.

  • In that dialog box, define Starting at (2/26/2021), Ending at (4/19/2023) >> select preferred options under By and then press OK.

Here, the Starting at denotes from which value the group should be started. The Ending at means up to which value the group should be continued.

Manually Grouping of Date Type Data in Pivot Table

As a result, you will get the group of years and months only. I have kept the Item field in the Rows area. So, the items are set according to the group.

group of date data type as years and months only


3.3 Use a Specific Range to Group Date Type Data in Pivot Table

The most interesting part is you can also define a range for the group of date values. But you can do this only if you select just Days in the “Grouping” dialog box.

  • So, just like in the previous example, bring the “Grouping” dialog box.
  • From the options under By >> select Days >> write the number of days as the class interval of the group >> press OK.

Making Group with class interval 20 days

Note: To make a defined class interval for date values, you have to select only the Days option. If other options are selected then uncheck them.

Below, I have attached the image of the created group with the defined range.

Use a Specific Range for Grouping of Date

  • You can also make the group by setting the class interval as a week. To do so, you need to set 7 days (1 week = 7 days) in the Number of days box.
  • If you want to see the group for a 4-week period, then set 28 days (4 weeks = 7X4 days = 28 days) in the Number of days box.

How to Disable Automatic Grouping in an Excel Pivot Table

You can disable this automatic grouping of date values in the pivot table.

  • To do so, from the Top Ribbon of an Excel sheet >> go to the File tab >> then you will see the following window >> click on the Options menu.

Going to Options menu of Excel

As a result, you will see the dialog box named Excel Options.

  • From the Data >> check Disable automatic grouping of Date/Time columns in PivotTables >> press OK.

Now, if you drag any field having date, you will not get the auto group.

How to Disable Automatic Grouping in an Excel Pivot Table


How to Ungroup Data in an Excel Pivot Table

I have already shown a way to ungroup the data in an Excel pivot table in section-3.2. Now, let’s see another way to ungroup the data.

  • Right-click on any grouped cell >> from the Context Menu Bar >> select Ungroup.

That’s all. Get all the grouped data as ungrouped.

How to Ungroup Data in an Excel Pivot Table


What Are the Common Problems for Grouping in an Excel Pivot Table?

  • While inserting the pivot table, if you check this “Add this data to the Data Model” then you cannot group any text or number data to that pivot table. In this case, you can group only the field having date. So, try to not check the “Add this data to the Data Model”.

Common Problem for Grouping in a Pivot Table

  • Don’t keep any blank cells in your dataset. This will create an extra group named blank. This may bother you.
  • In the case of making a group with numbers, don’t mix any text value with them. If there are text values with numbers, then you will not be able to create a group based on numbers.
  • Pivot tables with the same dataset are linked one to another. You cannot make a different group with the same data as all the pivot tables from the same dataset considering the latest group only. To avoid this problem see the following section.

How to Prevent Pivot Table Grouping from Impacting with Another Pivot Table in Excel

The group of a pivot table impacts another group of other pivot tables if all these pivot tables have the same source data. To prevent this grouping impact in another pivot table, you should make the pivot table in a different way.

  • Press ALT+D >> then press P >> after that you will get a dialog box named “PivotTable and PivotChart Wizard – Step 1 of 3”.
  • Click Next on that dialog box.

30 Step 1 of 3 of PivotTable and PivotChart Wizard dialog box

  • Then another dialog box named “PivotTable and PivotChart Wizard – Step 2 of 3” will appear. Click Next on that.

Step 2 of 3 of PivotTable and PivotChart Wizard dialog box

  • Now, you will get information from Microsoft Excel. Press No to that.

Pressing No to Microsoft Excel Information

  • Then, another dialog box named “PivotTable and PivotChart Wizard – Step 3 of 3” will pop up. Press Finish to that.

Last Step of PivotTable and PivotChart Wizard dialog box

Lastly, you will get the blank pivot table. Create the pivot table by dragging fields and then make the preferable group with them.


How to Filter Data in an Excel Pivot Table

Before filtering, you have to decide based on which value you want to filter the data.

If you select any cell having a region, then press the drop-down arrow beside Row Labels >> you will get a region-based filter option.

Getting region-based filter option

Again, if you select any cell with the name of the representative, then press the drop-down arrow beside Row Labels >> you will get the representative-based filter option.

Getting representative-based filter option

Similarly, if you select any cell having an item, and then press the drop-down arrow beside Row Labels >> you will get the product-based filter option.

Getting product-based filter option

Below, I have filtered the data based on the sales representative.

How to Filter Data in Pivot Table


How to Add Slicer in an Excel Pivot Table

There is another way to filter the data in the pivot table. Which is adding a slicer. A slicer keeps only the unique values of a field. When you choose any value from the slicer, you will get the pivot table having only these values.

  • To add a slicer, click any cell of the pivot table >> from PivotTable Analyze >> go to the Filter >> click Insert Slicer.

How to Add Slicer in Pivot Table

As a result, you will get the Insert Slicers dialog box.

  • Choose your preferred field. Here, you can choose multiple fields too.
  • Then, press OK.

Inserting Multiple Slicers

I have created slicers for the Item and Region fields. To find the total selling unit of Pencil from the East region; I have selected Pencil from Item slicer, and East from Region slicer.

Filtering data with slicers


Frequently Asked Questions

1. Can I sort values in a pivot table?

Yes, you can sort values in a pivot table. From the drop-down arrow beside Row Labels, you will find the options to sort the data.

2. Where are PivotTable tools?

When you activate any cell within the pivot table, you will get two contextual tabs. They are the pivot table tools. They are PivotTable Analyze, and Design.


Download Practice Workbook

You can download this practice workout below.


Conclusion

So, that’s for today. I hope it is clear to you on how to group data in pivot table in Excel. Feel free to drop comments, if you have any queries regarding this.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo