fbpx

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.

Grouping-Numbers-in-PivotTable
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.
Solution-Grouping-Numbers-In-PivotTable

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

Regroup-Numbers-In-PivotTable

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

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.

Grouping-dates-In-PivotTable

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

Year-Month-Grouping-dates

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.

Drag-Fields-Grouping-Dates

Now your table data looks like below:

Data-Grouping-Years-Month

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.

Grouping-Text-in-PivotTable
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.

PivotTable-Grouping-text

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

Excel Ninja Course
Limited Period Offer.

30-Day Money-Back Guarantee

Includes:


11+ hours on-demand video
Full Lifetime Access
Success on mobile and TV
Certificate of Completion
Downloadable Case Studies
Lifetime Support

Related Tutorials

Excel Formulas 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
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
Insert Symbol Dialog Box to Insert or Type degree symbol in Excel
May 7, 2018
CHAR Formula to Insert or Type degree symbol in Excel
May 7, 2018