Hello, and welcome back. last session we saw how to group numbers into different clusters. And that we took example of in terms of age and salary. This time, I'm going to show you how to group and cluster dates. And this is a very fascinating feature, which I've seen people not using. And once you watch the video, you yourself will appreciate that how wonderful this exercise could be.

So what I'm going to do is I'm first taking away all the fields from this grid area of pivot table, and I'm going to start from scratch. So we have a couple of fields which includes date of joining, and when I try to find four other fields, which are dates, I don't find any. So let me start with date of joining. I go to date of joining I put that in rupees. And what I get is, if these are correct dates, dates, which are in the correct format, as Excel can understand it. They are sequentially placed in an ascending order.

Now, I'm going to right click on any one date. Once I do that, I will go Back to the option which says group, I click on group. And I get a box, which shows me what is the starting and ending date in my data? And how would I want to group or cluster it. At this moment, I'm going to choose both months and years, I don't need to press Ctrl and then choose this. If you simply click any one options, it gets chosen.

So notice, as I double clicked, it quickly gave me your and month based classification and that too, in a sequence. So if you had a data which spans over multiple years, you'd be able to find a trend very, very nice trend. So let me find out how many people join in which year which month, and for that to happen. Let me bring name in the main action area. And then let me release it. So I've been told that there was one gentleman or a person who joined in the month of February in the year of 1995.

And the similar kind of trend I'm seeing as timeline moves on. Now you might tell me that this is over. Which is very lengthy and cannot be printed in a single a4 size paper. So now let me show you another trick. You notice as you broke the date into two components, it says yours and DOJ and similar thing I find in the rule section of this fuse box. Now what happens if I take DOJ, that is the month one and put it inside column?

Well, I get displayed I get month displayed on the column fields that is horizontally and vertically. Now, it's a perfect compact report which can be printed out as a single a4 size page and which exactly tells you the entire trend your wise and month wise. So if I try to find a trend that which month has been the busiest in terms of recruitment, and I compare the numbers, I look at the month which is October, in this data October November, these two months have been the busiest in terms of recruitment and which is Pick up maximum amount of people in my team. If I compare the different numbers, I see multiple instances of 25. And one instance that was year 2012, where 26 people were recruited in the same year. Now, it's not over yet.

I'm you also want to find a trend that how has January been in terms of recruitment vertically. So, what I may do is, I will put DOJ below yours, but then you say that this is the same report we started from then I will respond that yours let me interchange and put it below do g as you could see from the cursor sign up, I get month, month have different years. So January, how has it been over the different years the entire group of train, so again, there are so many combinations that you can apply once you have broken down a date into years and months. And not only this, if you want further trends, you can also apply percentage of row and column as we had discussed in the previous videos, and you will find a very, very nice trend coming up. That you can exhibit in terms of charts.

So that was deeds based automatic grouping.