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.
Also, I will show here some other queries about grouping data in pivot tables.
For conducting the session, I will use Microsoft 365 version.
Download Practice Workbook
You can download this practice workout below.
How to Group Data in Pivot Table: 3 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.
- 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.
- 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.
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.
- 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.
- 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.
- 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.
- Select Group1 >> write a preferable name in the Formula Bar.
- Similarly, I have created another group with other items and named that group Others.
Read More: How to Rename a Default Group Name in Pivot Table (2 Ways)
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.
- 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.
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.
As a result, another dialog box named “Value Field Settings” will appear.
- From the Summarize value field by menu >> select Count >> press OK.
Finally, you will see how many sales are conducted in these sales ranges.
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.
So, you will get the “Insert Chart” dialog box.
- The Clustered Column chart will be auto-selected>> press OK on that.
- Now right-click on data series (clustered column) >> from the Context Menu Bar >> choose Format Data Series.
So, you will see the Format Data Series at the right-most corner of the Excel sheet.
- Now, decrease the Gap Width.
Lastly, you will get the visual version of your grouped data. Here, I have changed the title name of the chart to Sales Review.
Read More: How to Group Numbers in Excel Pivot Table (with Simple Steps)
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.
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.
- 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.
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.
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.
Below, I have attached the image of the created group with the defined range.
- 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.
Read More: [Fixed] Excel Pivot Table Not Grouping Dates by Month
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.
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 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.
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”.
- 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.
- Then another dialog box named “PivotTable and PivotChart Wizard – Step 2 of 3” will appear. Click Next on that.
- Now, you will get information from Microsoft Excel. Press No to that.
- Then, another dialog box named “PivotTable and PivotChart Wizard – Step 3 of 3” will pop up. Press Finish to that.
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.
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.
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.
Below, I have filtered the data based on the sales representative.
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.
As a result, you will get the Insert Slicers dialog box.
- Choose your preferable field. Here, you can choose multiple fields too.
- Then, press OK.
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.
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.
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. Also, you can visit our site Exceldemy for more Excel related content.