In this Excel Training, I will show you the importance of grouping numbers, grouping dates and grouping texts in a pivot table report. If you have not used pivot table before then you are going to love this feature of excel. If you already created one before, you may want to start reading from here – Grouping numbers in Pivot table
A Quick Pivot table tutorial
First of all, let’s understand why you should learn pivot table? A pivot table allows you to extract the significance from a large, detailed dataset. If you are involved in data processing then Pivot table is the best tool to use in Excel. A pivot table can sort, count total or give the average of the data on a presentable dashboard. It is a very powerful tool that helps to summarise the data. I strongly recommend avoiding using formula to summarise data, rather follow this simple pivot table tutorial
Let’s learn how to use Pivot Tables:
Assume that you are working in an HR department and you have a data set of employee records with information like date of joining, employee name, salary, division, rating and Age.
Now you want to check division wise total salary, average salary and how many people are working on a specific division. If you are good in using formula then you can use the formula but pivot table will give you the result quickly.
To get a quick report, let’s apply pivot table:
- Choose your entire dataset. You can press a shortcut key Ctrl+A to choose the dataset or select the very first cell of your data range and then Ctrl + shift + right and down arrow to choose your data range.
- After your selection goes to insert tab and then click on the pivot table. The shortcut key is Alt + N+ V
It asks for 2 inputs:
- Which data should be used to create a Pivot table and
- Where do you want to create the pivot table. If you choose existing worksheet then you need to give the location where you want to see the report. I am selecting new worksheet so that my pivot table report will be on a new sheet.
As soon as you will press OK then a new sheet will be created with your pivot table field list. Here we will change few settings. Right click on your report area and click on pivot table options.
Now your layout is ready to prepare the report. Think about the first case where you need to show the result division wise total salary, average salary and how many people are working there.
To get this drag your division column in rows section and salary in value section for 3 times as you need the salary, average salary and count. If you follow this your pivot report should look like below.
Now as you drag salary column for 3 times in values that’s why it is showing as a sum of salary for 3 times. Double click on the 2nd sum of salary tab and then choose average in Value Field Setting of Pivot table in Excel and for the third one choose count.
Now for the first value it will show you total salary and for the second column it will show you the average salary and for the third column, it show the count. The final table will look something like this
Your report is ready. With few drag-drops clicks, you can prepare a report which normally takes not more than 10 seconds. That’s why most of the professionals prefer working with a pivot table.
Now from the same data, If we wish to see division wise and rating wise head count.
Just scroll this quick pivot table tutorial and follow the same steps:
Remove all the fields from your pivot table field list and be ready for another report i.e. division wise rating wise head count.
Now, this time, my division will be in row section, the rating will be in column section and name field will be in value section.
You can see your table is now ready and you are getting the report of division wise rating of headcount. Here’s how your report should look like
I think that this is enough for a basic Pivot table, Learn more basic in our free online excel course
Grouping in Pivot Table
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.
This primarily works with 3 kinds of data sets:
- Grouping Numbers,
- Grouping Dates and
- 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 organised data of no. of employees in different salary range, we would need to use grouping numbers technique.
Here’s step by step process to use grouping numbers in Pivot table report:
- 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….
Now we will focus on how to use Grouping Dates in this pivot table tutorial
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. 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.
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 row field, there are 2 fields one is Years and another is DOJ. Move this DOJ field in your column area.
Now your data looks like below.
Now let’s learn how to use Grouping Text in a pivot table report:
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 below. If you think this will help your friend – Please share and like.