Group Pivot Table items in Excel

Group Pivot Table

This tutorial will explain you how you will group items in the Pivot Table. You can group pivot table in different types like group pivot table by items, group pivot table by dates, group pivot tables by numbers,etc. This tutorial will explain you step by step tutorial to group and ungroup your pivot table. Grouping Pivot Table can be done automatically or manually.

Group Pivot table by Number | संख्या के आधार पर समूह पिवट तालिका

One of the powerful thing to do in Pivot table is to group Pivot table using numbers. For Example let us assume that you have a list of people with their salary, age, division in which they work, names of the employees with their date of join.

Steps to group by numbers are as follow:

Step 1: Create your Pivot table manually.

Step 2: Add Salary as Row Label Field and Division as the Column label field.

Group Pivot Table - by Numbers

Step 3: Right click anywhere in the row label field and select Group.

Group Pivot Table - Group

Step 4: Grouping Dialog box appears that shows the starting number and the enter number and ask you by what you want to group data. We select by as 100000

Group Pivot Table - Grouping

Step 5: Press ENTER. You will see that you grouping is done by group of 100000.

Group Pivot Table - Group by 1 lakh

Step 6: Right click anywhere in Row label field and Select Group. Select starting as 1 and ending as 100000 with 100000 difference. It means we are grouping the salary by 100000.

Group Pivot Table - by 1 to 1 lakh

Step 7: Press Enter

Group Pivot Table

Step 8: Now, Drag the name field in the Value field area of the Pivot table.

Group Pivot Table - Final Grouping

You will see that your Pivot Table is grouped by numbers. We read this Pivot table as

There are 166 total number of employees whose salary is between 1-100000. Division wise number of people with salary falling in the same range is also given.

This is how you can Group Pivot Table using Number. Let’s look on how you can group Pivot Table by Dates.

Group Pivot Tables by Dates | तिथियों द्वारा समूह पिवोट टेबल्स

We can group dates by years, half yearly, quarterly, monthly, weekly, days. Here we will group our data monthly and then we will again group our data yearly so properly show our Pivot Table.

Steps to Group Pivot Tables by dates are:

Step 1: Create a Pivot Table.

Step 2: Add Date of Join in the Row field area and Age in the Column field area.

Group Pivot Table - by Dates

Step 3: Right Click anywhere in the Row field area and Select Group.

Group Pivot Table - by Dates Group

Step 4: Grouping Dialog box pops up. It shows the starting date and the ending date. We need to choose by what we want to group our dates. Here we select months.

Group Pivot Table - Group Dialog Box

Step 5: Press ENTER. You will see that your row field area has narrow down to months. But here we don’t know the year for the months grouped. So we will regroup our data.

Group Pivot Table - Group by month

Step 6: Right click anywhere in the Row Field Area > Select Group then  In the Grouping Dialog box we select Years as by what we want to group. We see that month and years both are highlighted. This means that we are grouping are Row field Area twice.

Group Pivot Table - Group by Month and years

Step 7: Press ENTER. We will see yearly as well as month wise grouping of dates.

 Group Pivot Table - Years and Month

Step 8: We also need to group the Column field area. Right click anywhere in the Column Field area and Select group then Grouping Dialog box appears with starting age as 19 and ending age as 53. We want to group age by 10.

Group Pivot Table - Group by Age

Step 9: Press ENTER. We see that both row and column field area are grouped in the Pivot Table. and Drag the name field in the Value Field area of the Pivot Table.

Group Pivot Table - Month Age Name

The Pivot Table is grouped by dates.

We read the table as In the year 1995 Feb month 1 person had joined company with age group between 49-58. This is how we group Pivot Table by dates.

Hope you like our tutorial for more cool and amazing trick of excel Like our Excel Superstar Facebook Page and subscribe to our Excel Superstar YouTube channel. Excel Superstar is the leading online training company, which provides Online Excel Course in Hindi. Connect with us and become an Excel Superstar.