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.
15 Pivot Tables Tricks for Pros
15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.
Most Popular Tricks are #3, #7 & #12
Grouping in Pivot Table
This primarily works with 3 kinds of datasets:
- Grouping Numbers
- Grouping Dates
- 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?
How to use grouping numbers in Pivot table
- Just drag your salary in row section from your above data set.
- Right-click on any of the salaries
- Click on Group.
- 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….
15 Pivot Tables Tricks for Pros
15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.
Most Popular Tricks are #3, #7 & #12
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:
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 the 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 the comments below. If you think this will help your friend – Please share and like.
15 Pivot Tables Tricks for Pros
15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.
Most Popular Tricks are #3, #7 & #12