In this video we're going to introduce you to one of Excel's most powerful tools,
the pivot table and there is just no faster way to summarize data than
using this tool without writing a single formula in the process.
Now here's the deal,
in order to start with a pivot table though your data needs to be in good form.
What does that mean?
It means it needs a nice descriptive header row,
no blank row between the header and the data.
The data should be consistent all the way down the columns of the table.
So all numbers, all text, all dates, and no blank rows, no blank columns.
And once we have that, we can click any cell inside and
choose insert pivot table to get started.
But before we do, I want to call out something very, very important.
If you look in this table range area, this is pulling the source data and
you're going to see there's dollar signs in here.
There's four of them, that is Excel throwing money at you to
get your attention to know that you're about to do something very dangerous.
You do not want to do this.
If you see money, stop, okay?
We're going to hit cancel and
what we're going to do is we're going to turn this into a proper
official Excel table because that is a better choice of data for a pivot table.
So I'm going to just change this into a nice blue style here, that looks good and
of course I'm going to go and name my table right away to call it sales.
Now, I'm going to click somewhere inside here, insert any pivot table and
notice the difference, it now calls it sales.
And this is just going to make it a lot easier to make sure that your data updates
when you add new columns or new rows in hard coded ranges,
you actually have to maintain that data source when using a table it just happens.
Now the next option you've got, we can use to or
put our pivot table on a new worksheet or an existing worksheet.
I'm going to put this on an existing worksheet just so
we can see exactly what's going on here.
And before I click OK I just want to call out the last little thing here about this
add to the data model.
Unless you have taken training in a technology called Power Pivot,
I do not recommend that you check this button,
it actually adds a whole level of complication to your pivot tables.
So let's drop this in i five, we're going to go say, OK and
we get this pivot table frame.
Now, here's the cool thing, what we can do now is we
can actually write a bunch of formulas by just dragging and dropping fields.
So what I'm going to start with is I'm going to take the sold by and
I'm going to drag it down into the rows area.
And what you'll see is it gives me a unique list of all of my salespeople then
from the sold by call so we've got a Fred, a Jane, a John, and a Mary.
Now, the next thing I'm going to do is I'm going to grab price and
I'm going to drop it into the values area.
And it now basically does a great big sum if to go and
sum all of the prices if the name is equal to what you see on the left hand side.
And yet there are no formulas here at all so this is pretty cool.
If I want cost, I could go and drop that into the pivot table as well.
If I don't like the order, I can drag it around and
put it in a different order so now we have sum of cost and then sum of price.
And if I don't like this I can actually take it off and
throw it back in the field as well.
Now, let me try this, I'm going to move sold by into columns and
I'm going to move inventory item down into rows.
And this is the beauty of a pivot table as it allows you to very quickly try
different looks and different views of how you want to see things.
There is one thing you need to know about your pivot table though.
Let's change the price of this lovable kitten here to something extremely
obvious, $9,000, it's a very expensive kitten.
And what you're going to notice here is when we come down here and
we look for our lovable kitten, what we should see is that our lovable kitten for
Fred should have at least $9,000 and it's only got 22 or $225 in there.
Why is that out of sync?
Well, the answer is that pivot tables cache their data.
They're not live against your data source.
So this is the important thing to recognize about a pivot table is to make
sure it's up to date.
It's either right click and choose refresh on it,
that will now update everything there from the data source.
Or if we go back and set this level will kitten back to $45, we can also go up to
the data tab and choose refresh all and that will refresh all pivot caches
in the workbook to make sure that your pivot tables are up to date.
Pivot tables are amazing for very quickly summarizing things and dragging and
dropping to get the view that you want.
The only big key there remember, put it against the table to start with and
then make sure that you don't forget to
hit that refresh button when your data changes.