Hi. So after having a very good discussion on the different features and benefits of pivot table and the way they are achieved, let's get down to real project. This time, we are going to work with a data which spans over 60,000 plus rows and number of columns, almost more than a dozen. Now to be precise, I think it's around 30. Yeah, I can see that 13 columns. So this data contains all the features text, number and date, which is normally the details backbone.

And this primarily talks about a sales data sales of a sports equipment seller. It's been selling those equipments to various retail stores across the globe. So you'd find a column it's called region sub region rather, and it is consisting of all the names of the country to which its products are being sent to. So what does it sell? Let me further exclude to the column heading category. It says mountain bikes.

Within that you'll also find socks and mountain bikes mountain Mountain frames. And all these categories of products are actually falling under different umbrellas bikes to be one of the umbrella, then the clothing and components. And further below, you might also find something called a series. So primarily this company deals with four major divisions. And each division has sub products. Those are available in different colors and different model numbers.

Now, it's been selling those quantities in the three years 2000 to 2003 and 2004, the quantity is given and the sales amount is also provided. So the cells which are marked in green are the important ones, which primarily tells you what kind of product has been sold to who and which country and what quantity and what amount. Now the kind of analysis that we'll be trying to do. Let's do this with pivot table. First things first Ctrl A, choosing the data Insert, Pivot Table. Once I do that, I'm just looking at the data source.

It's simply pointing to a one till m Some row numbers which is 609 to zero in this case, I will press OK, it will populate that pivot table grid in a new sheet. We have seen in our earlier introductory sessions that I need to activate one setting a vintage setting classic setting. So I go to the pivot table grid, right click, I go to pivot table options further. And deep down, I then go to Display tab, where I will target the options same classic pivot table layout. Once I switch this on the layout change is completely the one we we were comfortable with. Now notice, I'm going to pick one attribute which is based on date.

So I have sales date somewhere out here. Yep, there you go. I put that in row fields. So as expected, that is going to give me a sequential date. You have seen in our earlier discussion that if I right click on any one date item and then I go to group, within group, if I choose months and yours both the entire deed gets split into two components. One is year one is month.

So now if I populate any set of data, which is based on number, it's going to show the full trend. Just for example sake, I'm going to put sales amount in the main action area. Although I would like to format this entire column so as to avoid the decimal level accuracy. Now what it shows me is the full train starting from January to December of 2004. I know what you're thinking, why not show this data in a form of chart? Well, there is a separate section of charts in our video program, but at this moment, I am going to simply press F 11.

If I do that, in a few seconds, on my screen, you will be seen a chart pivot table chart. There you go. So the pivot table chart if I magnify further, let's see how has the axis been defined. So within 2002, January, December within 2003, same set of months and so on. So full trend has been captured using the chart showing you the sales amount over the last three years. 2000 to 2004 in.

Let me go back to sheet one. So I want to get some more juice out of the support. So what do I do? I close the look at the year and I look at the month, then things strikes me that why don't I put yours yours field which has been broken down into column layout. If I do that, the entire thing changes you notice the year component has been given horizontally. The vertical data talks about the month wise details and now I have a breakup year to year.

So I can also notice that in year 2002, I sold $24 million worth of goods in the next year $22.5 million, and the next year $24.2 million, total $81 million. If I want to analyze from the point of view of let's say percentage, let's see all the different combinations we can make use of which he had already discussed in our previous video sessions. I pick up the cell which is reminding me of some kind of a mathematical calculation, I double click, I go to the second tab, yes. And then I pick up percentage of column. Now if we recall correctly, percentage of column is going to make a calculation like this 713 thousand, seven plus thousand, and then divided by $24.3 million. If I do that, I get a trend, a trend, whereby I get to see that in the respective three years, which month has been performing the best.

So in the 2002, I noticed November was a very, very good month. I can't see the same for the next year because in the next year, the best month was that of August. In the next year, it was me. So this is based on percentage of column. If I double click and I change the show value as percentage of row to total, okay, now it tells me that if you consider all the sales of the 3g January was January of 2000 to 2003 and 2000 for the best January has been of 2004. If I talk about October, which October has been the best?

Well, in this case, the 2003 shows me highest number, which means in 2003, the October sales were quite good. And if I compare October to October to October of all the three years, 2003 stands apart. So this was a combination of techniques where you group the dates. Next, you also saw the couple of variations of percentage in terms of column percentage of row, you also split the data into fields and column fields. And to top it up, somebody might tell you look, I like the support. How about you make this report one report for all of my regions, let's say Canada, US, UK, France, Germany, and so on.

Well, in that case, I put sub region Yeah, there's one field called sub region. I put that in the report filter. If you remember closely from our previous video session, there is an option which allows you to replicate the sheets into different slices. So I go to pivot table tools analyze, I'll click on the drop down and says report filter has already been containing sub region, I click on show report filter pages Hold on tight. If I click on this and I press enter, in few seconds, what I get is different sheets called Australia, Canada, France, Germany, US and UK and so on. So once the pivot table format has been finalized by you, and if you want to replicate the similar format for a couple of divisions or countries or username, you will be using this technique.

So this was just a practice exercise based on all the things we have learned about pivot table from our previous video sessions. do go over this once again if you feel the need because once you have a good grip on this features, you will be the pivot table master ninja. see in the next video with more features