In this course we're going to look at the pivot table,
one of the most amazing tools that actually exists inside the Excel product.
Now if you've never used a pivot table before you might be thinking,
what the heck is that?
Why do I even care?
And let's be honest, it's not the most enticing feature name ever and
it's not really very descriptive either.
So let's take a look in this video at what they do and
why we want to explore them further.
Why are they so amazing?
In this case here I've got a nice big table of data here that breaks down food
and alcohol sales for a restaurant company.
Now, our point of sale chit date here, you'll notice it goes and
spans over from 2013 all the way back to 2009.
How many records do we have?
Well if I press End, down arrow,
you can see that we've got about 349,000 records in this data set.
And they're obviously not in order because we started with 2013 and
got to 2012, that's where the break is, but
2009 is mixed up in all the middle of the stuff as well.
Now the question that I've got for you is, if I asked you to quickly summarize this
by year, break it down between food and alcohol and give me both the total sales
and the percentage of total sales for each of those things, how fast could you do it?
And the reality is if we tried to write a bunch of sum if formulas to do that that's
going to be really hard.
It's going to take a lot of time, it's not going to be very robust.
So let's look at what the pivot table actually adds for us in this video.
I can click somewhere inside my table here and say insert pivot table.
This will create a new pivot table for me on a new worksheet,
which I can then start configuring it to see how I want everything to look.
I'm going to grab my date field, drag it on to Rows.
And you'll notice that groups it up for me nicely by year right away.
I can then come down here and say, hey, I want to see my amount.
So I'll drag my amount into the Values area.
And just like that I have a total breakdown by year for my amount.
I'm going to throw Class onto my Columns.
And there we go, it's now broken down by food and alcohol.
And I'm even going to go and grab Amount and
throw it onto the pivot table a second time.
Why a second time?
Well because now I can actually start playing around with these things.
This one is going to be my Sales $.
And I'm going to quickly change the format of this guy here so
that it looks a little bit better.
So we'll set this up with a number, with thousand separators,
and I'm going to get rid of all the decimals.
Now I'm going to do the same thing over here, except that I'm going to change
this to summarize and show my values as a percentage of a column total.
Just like that, I have my percentage.
So I can now come back and say, this is the percentage of total.
Now, just like that we've already broken everything down.
But what if I wanted to get even more in-depth with this?
I'm going to insert a new column on the right hand side here.
We'll just open that up row 1, make it a little bit bigger, and
I'm still going to open up row one to make that a little bit taller as well.
I'll click inside my pivot table, and I'm going to go up to Pivot Table Analyse.
And at this point I'm going to go down insert a new slicer.
My slicer is going to be for Class, and another one for my Category.
And just like this, we'll just snap this one into the grid and resize it.
And we'll move Category over the same way.
Snap it there and resize it a little bit.
And now what you can see is that I can drill into my Food,
can drill into my Alcohol.
I could go and say, I'd really like to see draught beer, for example.
We'll clear this filter here.
Notice that it's changing things all the way along.
And maybe I want to see my burgers and beer together.
And just like that, my food now color popping back in place.
But further than that, I can even do more.
I can come back and say once again we'll go to pivot table tools analyze,
insert timeline, and we'll add one for the point of sale chit date.
Just like this, I can now snap him up to the grid as well, we'll make him snap
nicely in place, make this a little wider, change this guy up to be years.
And now you can see I can break it down to say, just show me the 2010 sales, or
the 2011.
Or maybe I want to see three individual years.
This is why pivot tables matter to you and why you should care.
Really, really quick ways to break down data and actually go and slice and
dice it the way you want to see it.