In this video we're going to look at one of the most incredibly cool tools inside
Excel called the PivotTable.
Now these things have a bit of an intimidating name, but they shouldn't.
They are absolutely fantastic.
And they are the quickest way that we can actually go and summarize, and slice and
dice data into a variety of different formats.
It kind of allows us to create kind of a Rubik's Cube of our data,
which is really kind of neat.
So in this case here, we have a nice set of data here.
And some of the key characteristics that we always wanna work with when we're
working with a PivotTable, is we'd like to have our data in a good tabular format.
So again, that means we need a header row,
we need consistent types of data down the columns, no blank rows in between.
And the ideal way to actually work with your PivotTable is actually
have your data in a proper Excel table to begin with.
So I' gonna go and set up an Excel table right now.
And we'll just call this particular table here,
how about something like, pet inventory.
Now in order to create a PivotTable, what we're gonna do is we're gonna click inside
a cell, we're gonna go to Insert, and we're gonna choose PivotTable.
And it'll ask us to select the table or range.
You'll notice it's picked up the name of the table that I created already.
We could use an external data source like a database as well.
And I'm gonna say that I'm gonna put this on an existing worksheet, and
what I'm gonna do is I'm gonna click the little down in the corner here,
I'll go and drop it into the example PivotTable page, and we'll say OK.
And what you'll see is you get this PivotTable frame that ends up
showing up here.
Now, the nice piece about a PivotTable is we also get this little area over here
that is our PivotTable builder.
So what we can do is we can basically just drag an item from the field list up top,
and let it go in say the rows area.
And it will create us a unique list of every value that's in that PivotTable.
So it just cuts it down to a unique set.
So what if we said, well you know what maybe I don't want dates,
I'm gonna pull this back off the PivotTable and I'll let it go.
So if I decide that I don't like the way it's going, I can change it.
I could grab my inventory and pull it down onto rows, and there you go.
You can see all the unique inventory items that we started
off with in our original table.
I could pull say, sold by,
and I could start figuring out who sold the adorable kitty cats.
We've got a Fred, Jean, John and Mary have done so.
I could then go and take, let me see, how about price and
put it into the values area.
Well, we'll just drag that down in here and let go.
and you'll notice that it very quickly goes through and it actually sums up All
of the adorable kitty cats Fred has sold, and he sold $175 of adorable kitty cats.
Well, what if I don't want it to look exactly like this?
What if I wanted my Sold By to be sliced across the top here,
so I saw all of my inventory items down the left, and
all of my salespeople across the middle, and then a big table there.
Well no problem.
We'll just grab Sold By and we'll drag it over into the Columns area, and
there we go, that's exactly what we've got right now.
So this is the beautiful thing about PivotTable is it's very quick
to change the way that you look at data.
Don't like where it is?
Drag the field off the PivotTable and let it go.
Maybe we don't wanna look at price, we wanna look at say Commission.
So, I'll just get rid of price and I'll put commission on here.
Of course, if I wanted I could also even have price too.
I could drag that in and then have both pieces.
Okay, so here's the sum of commission and the sum of price.
Really, versatile tool this and
so quick, but there is one thing I'm just going to drag this back in here.
There's one thing you need to know about PivotTables.
I'm gonna dismiss the field list right and get it to go away.
And we can see that we've got our values here.
I'm just gonna go back over to the example data set for a second, and
I'm gonna change this commission up here to something ridiculous.
so we're gonna go to $100,000, so this is Fred's lovable kittens that he sold here.
And we'll go back to the PivotTable, and we'll look up lovable kittens and
here they are, and here's Fred and you can see that.
That sum of commission has not changed.
You go well why is that.
Well here's the thing, because PivotTables need to be so
quick they actually preprocess data when we use them.
So the data is not actually connected live to the data set.
In order to actually get these to work what we need to do is we need to
right click on our PivotTable.
And say refresh data, and at that point it will go and it will stream all of
the data from the original table into the pivot cache which serves up the PivotTable.
So again if we were to go back and saw well that's not right,
let's go put this back to the $1.35 that it was.
and we go back over to our PivotTable, you'll see that it hasn't changed,
again we need to right click and we need to say Refresh Data.
So this is one of the big key points to remember.
These are extremely quick to refresh data.
The drag and drop interface is awesome, and
lets you look at data in all kinds of different ways.
They're super easy to use, but they're not live.
You have to right-click and refresh, when you want your PivotTable to update.