- [Dennis] One of Excel's
most powerful analytical tools
is what we call a pivot table.
It's a great tool for creating quick summary totals
based on a list of data.
We've got a list of about 900 rows.
It's not sorted, as it often might be,
it doesn't need to be to create a pivot table.
A list like this should have no empty rows
or columns within it.
What we'd like to be able to do is to come
up with some good, quick summary information here.
So, off to the right I've done this manually ahead
of time with kind of list you could imagine wanting to get.
It's a list of all the sales persons here,
they can sell products to any region,
so we see how that's broken up by region.
And how do we get to something like this?
Here's a formula here in cell J4.
Double click it.
Not the most difficult of formulas, but on the other hand,
it's going to take you some time to come
up with answers like this and verify them.
And we wouldn't have a lot of flexibility here
if we had to quickly turn this around
and put in another field up here,
for example product or customer instead of this.
We can create a list like this with a pivot table
in about five or 10 seconds without formulas.
And that's what we're going to do.
Check out the list ahead of time.
You'd make sure there are no empty rows or columns.
And you only click within the lists.
And although it is a data management like tool
in one respect, it's not found on the data tab,
it's on the insert tab.
Let's click pivot table.
Excel nearly always gets it right
and figures out the extent of the data.
We could put this on the same worksheet,
but more likely,
let's go with the default setting here, new worksheet.
Many times you'll just glance, click okay.
We're on a new sheet.
Off to the right
is the all-important pivot table fields list.
Shows those same column headings as we saw before.
We can make this wider and narrower.
A couple of clicks and a drag
and we will have a pivot table.
I'm going to click the box for sales person
of the left-hand side of the screen.
That's a unique and alphabetized list
of our sales persons.
I'll zoom in a bit using the slider bar,
bottom right of our screen.
I'm going to click the box for items.
Watch those numbers over there.
And I'll drag the word region into the columns area.
That's it.
Now you might've noticed before and I'll point
out or best salesperson Willy Loman, right there.
Grand total 8,111.
Not a formula in sight.
Let's jump back to the source data.
One sheet to the right.
There's those same numbers out there.
There's Willy Loman, 1,073, grand total 8,111.
We got here pretty fast.
Not quite right, maybe with these, nothing wrong with those,
but notice something else.
When the active cell is within the pivot table,
they've got a new ribbon pivot table tools
with a contextual analyze tab and a design tab.
Now you wouldn't know instinctually what to do here,
but on the design tab, report layout compact as the default.
If we choose outliner tabular,
we'll see the actual field names there.
I think most people would probably want that.
So salesperson, region.
Looking good.
And if we were making a presentation,
you can imagine somebody in the audience
might say something like, okay,
we see the breakout by region.
How about by product?
And what do we do?
In the pivot table field list to the right,
we'll uncheck the box for region
and drag the word product into this area called columns.
Now you quickly start beginning to see how the lower portion
of this pivot table fields list
is like a mapping of the pivot table.
We've got products here across these column headings.
Sales persons are in rows.
So you see the term rows, columns out here.
And no time are we seeing any formulas.
And not only can our comparison be just
between two fields here, product and sales person,
we could bring region back here.
I'll put it in the rows area.
Watch the screen on the left-hand side.
And I'll zoom back again with that slider bar a bit.
Let's see what's happening.
Now that's maybe contrary to what a lot
of people envision as a pivot table, usually it's compact,
give me the big picture sort of thing.
But in some cases, this would be appropriate.
We can make some other changes.
Here's the design tab.
Off to the left are subtotals.
We might not want to show those.
Or maybe we'll just move these fields around
in different ways,
making all kinds of different comparisons.
Maybe if we compare or change the order of this,
drag sales person below region,
we see a different layout that way.
So there're just so many different ways to view the data.
We never worry as we do this too about disrupting
or threatening our source data.
And one little oddity that you get used
to at least initially is this idea.
If we do make changes to the source data,
the pivot table does not adjust automatically.
You go to the analyze tab and refresh the data,
or use that keystroke shortcut Alt + F5.
So that's something to keep an eye on.
But it's amazingly powerful tool.
It gets us here quickly.
We create these summary lists.
We manipulate the data in a variety of ways.
It's just indispensable for any kind
of situation where you're trying to analyze data
from a list.
(upbeat music)