How to Group Numbers, Dates & Text in Pivot table in Excel

Now will talk about the Grouping Data features of the pivot table. This technique of grouping numbers, dates and text are not similar to a grouping of data by using data tab. This is something different. I am assuming that you must be knowing how to create pivot table in excel if you are not aware of please visit, How to create Pivot Table in Excel

Grouping in Pivot Table:

This primarily works with 3 kinds of datasets:

  1. Grouping Numbers,
  2. Grouping Dates and
  3. Grouping Texts

Now for the grouping numbers and dates, it has a tremendous advantage. It can automatically be grouped into a cluster of numbers and dates (aka grouping numbers technique). Let’s look at it with an example of Salary table as discussed above. In order to get an organized data of no. of employees in different salary range, we would need to use grouping numbers technique. Do you know how sorting is done in a pivot table? Know more

How to use grouping numbers in Pivot table

  1. Just drag your salary in row section from your above data set.
  2. Right-click on any of the salaries
  3. Click on Group.
  4. Enter the Starting and Ending number of the salary range in opened dialog box and set intervals to break range in a small group of numbers or clusters.

As soon as you click on Ok, you will notice the grouping numbers of salary or salary clustering have been done automatically and the salary starting from the minimum salary with an interval of one lakh.

Now this clustering or grouping numbers can also be made more presentable if you regroup salary by starting with number 1.


Now your data looks like one to one hundred thousand, one hundred thousand to two hundred thousand and so on….

Conclusion: You must have learned how to group numbers in pivot table in excel.

How to use grouping dates in pivot table

Please take away all the fields from your pivot table field list and start from the scratch. Now, take D.O.J (date of joining) column in your row area. What I notice is these dates are in the correct format as excel can understand and they are sequentially placed in an ascending order, just like we did while grouping numbers in pivot table. Now right-click on any one date and then click on Group. Another box will open up which will show you start and end date from your data. And then how do you want to group it.


It will quickly give you year and month wise classification in a sequence.


Now if your data is too large it is really very difficult to take a print out on A4 paper. To take the print out in A4 paper you need to arrange it in such a way so that it looks readable.

If you look very carefully in your field row, there are 2 fields one is Years and another is DOJ. Move this DOJ field in your column area.


Now your table data looks like below:


Learn how to create pivot table and how to apply sort in pivot table, how to group & ungroup in pivot table, how to apply slicer and filter in a pivot table, here’s your solution just enrolled in advanced excel course and you learn more than 150+ excel tips & tricks. Enroll Now

How to use Grouping Text in pivot table

We have already seen the features of grouping Numbers and grouping dates but this time, we’ll talk on grouping Text. Though it can be grouped but you need to do it manually. Let’s say you have eight division and four of them led by Mr. Simon and another four led by Mr. Sen. From my data range there are eight division and they are AD, CDFD, ED, HFD, LGAD, PEMD, RAD and RDD.

Let’s assume AD, ED, RAD and RDD divisions are led by Mr. Simon and rest 4 divisions are led by Mr. Sen.
Just drag the division in your rows field. Then select the four divisions i.e. AD, ED, RAD and RDD by pressing Ctrl button. Do not select the entire row. And then right click and then click on Group.

Immediately they will cluster together under Group 1. Now select the last four division and again click on Group. This will create another group called group 2. Now you can change the name of the group as Mr. Simon and Mr. Sen.


Grouping numbers, Grouping Dates and Grouping Texts have same techniques, as a result, its application in data analysis varies. Break big data in small silos for analysis.

Hope you have enjoyed this pivot table tutorial. Please write your feedback in comments belowIf you think this will help your friend – Please share and like. How to use a pivot table in excel

Related Tutorials

Connect to Data in Power Query
February 21, 2019
How to use Text to Column in Excel with 4 Difference Example
February 21, 2019
Things you should learn in Excel Course
February 13, 2019
How to use Clean Function in Excel
February 11, 2019
How to use the Excel Choose Function
February 11, 2019
How to use the Excel Code Function
February 11, 2019
How to use the Excel Column Function
February 11, 2019
How to use Ceiling Function in Excel
January 30, 2019
How to use Char Function in Excel
January 28, 2019
Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
December 10, 2018
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas and Functions in PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018