It's now time to look at one of Excel's most powerful tools, the pivot table.
And what's that?
Well, it's basically where we can pull our data into Excel and
treat it like a Rubik's Cube.
So we can quickly pivot and
change the way that we're looking at data to get different insights out of it.
The secret to starting with a pivot table is to have a good data source.
What makes a good data source?
You've heard this before.
A good header row, no blank rows or columns in the data.
And consistent data down the actual columns as far as types go.
The easiest way to ensure that, formatting the table.
So we'll take this data and we will quickly turn this into a nice table.
Notice it has headers, that's great.
And we'll go and rename this table immediately to something like Inventory.
Now, to create the pivot table itself, this is super easy.
We can click somewhere in our pivot table, go to the Insert tab, and
choose to insert a Pivot Table.
Notice that it picks up the table's name.
This is great because as your table expands,
all the new data will automatically be fold into it.
I'm gonna go places on the Existing Worksheet and
I'm gonna drop it right here in cell H5, and will save OK.
This creates the PivotTable frame.
And you'll notice that we have the ability over here to look
at all these different fields, which come from the headers of the actual table.
What I'm gonna do with this now is I'm gonna take sold by and
I'm gonna drag it down here and drop it in rows.
And you'll notice that there we go, we have a unique list of every single
salesperson, no matter whether this is 15 or 15,000 rows of data.
I'm going to put inventory item underneath my rows by just checking it right here and
you'll notice that it now shows every sales item for every sales person.
On my Values, I'm going to check the box next to Cost.
Because it's numeric, it automatically throws it into the Values area.
And then I'm gonna look at this and say,
I'm not sure I like the layout of this pivot table.
I think I would actually rather see my employees across the top so
I can see what the sales are by item, by employee.
So what I'm gonna do is I'm actually gonna take Sold By,
I'm just gonna left-click and drag it up here to Columns and
that re-pivots the table into a different format.
And this is the real strength of a pivot table,
is the ability to actually change things.
And then I realized, wait a minute, I don't really wanna see cost,
I actually wanna see the price.
So let's uncheck Cost or drag it out, whichever, and check Price.
And there we go.
Now we're actually showing the sales price.
So that's pretty good, now the pivot table is super useful for
all of the values that we can get out of it nice and quick.
It is way faster than writing a whole bunch of SUMIF formulas in order to make
this work.
But the pivot table does have one nuance that you need to be aware of.
What I'm gonna do is I'm just gonna dismiss the field list here.
Now, we can always bring this back anytime we want by right-clicking
on the pivot table saying show field list.
I just want to get it out of the way for
a minute because I want to be able to show you something really important.
Let's say that Fred's kitten that he sold here was a really important one.
It was a very, very lovable kitten, and it was worth $1,500,000.
You would think that when you change this that it would be pretty obvious for
our lovable kittens for Fred that it would actually update right away.
And plainly it did not.
And the reason being is because the pivot table
actually uses something called the pivot cache in its process.
What that means is streams all of the data when you create it Into the pivot cache
and it pre-calculates everything that you could want.
And that's why, when you're dragging and
dropping fields, where there's 15 rows or 15,000 rows, it's just as fast.
The problem is, it's disconnected from the calculation chain.
So in order to update this, we need to remember that pivot tables are not live.
And to do this, we go right-click and choose Refresh.
And now you'll see that our 1.5 million comes in.
There's also another way to do this, too.
If I go back and say 45,
hit Enter, we'll change it back, it obviously hasn't updated here.
My personal preferred way to update things is to actually go to the Data tab.
And click the Refresh All button, and that's because I can create multiple pivot
tables off of multiple tables, and this one will refresh everybody all at once.
So the pivot table is hugely useful It allows you to quickly go and
reshape your data.
If you don't like the format you just drag fields around to different places or
drag them off or uncheck them and away you go.
You've got lots of different way to report from a small of a large data set.