Pivot Table Grouping – Numbers & Dates

In this blog I will talk about various features of pivot table. If you have not used pivot table before you are going to love this feature of excel.
First of all why should you learn pivot table? A pivot table allows you to extract the significance from a large, detailed data set. If you are involved in data processing then Pivot table is the best tool to use in Excel. A pivot table can automatically sort, count total or give the average of the data stored in one table or spreadsheet, displaying the results in a second table showing the summarized data.
It is a very powerful tool that helps to summarize the data. If you find yourself to write formulas to summarize data then Pivot table will help you and save your time.
Let’s learn Pivot Table

You are working in HR section and you have a data set of employee records 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 the above report let’s apply pivot table. First choose your entire dataset. You can press short cut key ctrl+A to choose the dataset or select the very first cell of your data range and then Crtl + shift + right arrow and down arrow to choose your data range.
After your selection go to insert tab and then click on pivot table. The short cut key is Alt + N+ V

First it is looking for the selection and then it is asking your permission that here you want to create the report. 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 report will be in a new sheet.
As soon as you will press ok then 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.

Then another dialog box will open and it need to go display tab and then choose classic layout. And then click ok.

Now your layout is ready to prepare the report. Think about 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 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 sum of salary for 3 times. Double click on 2nd sum of salary tab and then choose average 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. And the result will be looking like below.

Your report is ready. With few drag and few clicks you can prepare report which normally takes not more than 10 seconds. That’s why most of the professionals prefer working with pivot table.
Now from the same data we want to see division wise rating wise head count. Remove all the fields form 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, 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 wise head count. And your report should look like below.

Now I will talk about the grouping features of pivot table. This group does not mean that you go to data tab and then group your data. No this is something different. This primarily works with 3 kinds of data sets. First one is Number, 2nd one is Date and third one is Text. Now for the number and dates it has tremendous advantage. It can automatically be grouped into cluster. Let’s look at the example.
From your above data set just drag your salary in row section. Then right click any of the salaries and then click on group. Another dialog box will open up which will say automatic grouping where the starting number is the lowest number from your salary and ending number is the largest number and by means what intervals will it show the grouping.

As soon as you click on ok you will notice the clustering has been done automatically and the salary starting from the minimum salary with an interval of one lakh.

Now this clustering can also be made looking beautiful if you again right click and again click on group and this time put 1 in starting number.

Now your data looks like one to one lakh, one lakh one to 2 lakh and so on….

Now we will focus on grouping on Dates.
Please take away all the fields from your pivot table field list and start from the scratch.
Now take dOJ (date of joining) column in your row area. What I notice is these dates are in correct format as excel can understand and they are sequentially placed in an ascending order. 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 diifiicult to take a print out in A4 paper. To take the print in a A4 paper you need to arrane it in such a way so that it looks readable.
If you look very carefully in your row are athere are 2 fileds one is Years and another is DOJ. Move this DOJ field in your column area.

Now your data looks like below.

Grouping Text
We have already seen the features of grouping of Numbers and dates but this time I will talk on grouping on Text. Though it can be grouped but you need to do it manually. 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 divisons 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.

Hope you have enjoyed this tutorial. Please write your feedback in comments below.