Now we're going to talk about one of the coolest features that exists inside Excel; the PivotTable.
I'm not going to tell you a whole bunch of stuff about what PivotTables do because
it's a heck of a lot easier to actually show you. But before we get
to that we need to just understand some characteristics about the data that we have to work with that
actually build into good PivotTables. And basically
part of the wording in this thing is PivotTable so that kind of leads you to
believe that you need a table to start with. And effectively that's where we are here.
What I have is I have about 700 and some odd rows of data here and this all comes from a point of sale system from a restaurant.
You'll notice that it is actually formatted as a table. If you were to
scroll through the whole list you'll see that there's no blank rows or no blank columns
that are separating this data up. So it's a good solid contiguous block of data and that
is the number one ingredient for having a good data source for a PivotTable.
The second thing is to make sure that we have a good header row across the top that describes our columns really well.
Now that we have that, what we're going to do is we're going to create a PivotTable and we'll see why these are
so awesome. It's real easy we click somewhere inside our
list of data here. We go to Insert and we click PivotTable.
And then it says where's your range? And it's picked up our table for us, we're going to throw it on a new worksheet, we say OK.
What we get is we get a PivotTable frame and here's how PivotTables work:
What we can do is we can scroll down and say you know what, I'm going to grab Units here and
I'm going to drag it into the values field. I'm going to let it go.
And it's just summarized my 772 records I believe, to say that there's 782 units
total inside those 772 records just like that! And this is cool because
700 records, 700,000 records, it doesn't matter it goes almost as fast. Now
what else can we do with this? Well lets go grab the POSReportingGroupCode and throw that on the Rows field.
Oh okay now we get a breakdown between alcohol, food and non-alcoholic beverage.
We can further break that down by grabbing a category description and dragging that underneath the reporting groups.
And there we go now we can see what the breakdown of these particular items is between our alcohol, food or non-alcoholic beverage.
So it's pretty cool stuff. So this is the basics of how to create a PivotTable, first we find our data source we add it to or create a PivotTable and
we drag our fields on into the different areas on the PivotTable grid.
What about modifying it? Well, that's easy too. We just grab these guys here;
Sum of Units down in the bottom corner, I'm going to rip that right off the PivotTable and put it back to the Field list
and it takes it off the PivotTable. You can see the pivot updates.
We'll remove Category as well, drag that back up top. So now we know just what the values are we have in there.
And now let's go and throw the GrossAmount down in here. So now we're looking at dollars.
There we go, we've got $6000 worth of sales on this particular day.
What else could we do? Well you know what, we could take our POSReportingGroup and instead of having it down
the left hand side what if we did this? Lets drag it and put it into the Columns field.
So now the PivotTable reshapes and it's telling us that we still have the same
values they just kind of turned on their ear a little bit. What else can we do with this?
Well lets grab the Hours field. We'll drag that into Rows
and now I can actually see what my sales were by product type, by hour, which is pretty cool.
I can also go further with this. I don't have to look at just sums of values. I can actually go right click
say Value Field Settings
and I can go and choose to have an Average. So lets see what the average of the sale is.
Oh that's kind of ugly. I'm just going to go and switch in here real quick and go to Value Field Settings, I'm going to go into Number Formats
and I'm just going to change this to Accounting. Just so we get something consistent and say OK.
And here we go now we've got some sales. So we can see that
our average sale of alcohol is pretty low in the morning which you'd
expect because most people don't drink early in the morning. It generally stays the
same to around 6:00 at night and then we have a big spike. I guess people come in
for dinner and they want to have a beer or something that's what ends up happening there. That's kind of neat.
Our food sales also spike in the dinner hours compared to what
we have, except for it looks like we have breakfast at this particular restaurant.
Some neat things, you can see that you can actually get into here.
This is where PivotTables are so cool because you can drag these fields around from
one place to another and get them into the places that you want to show the data
from two dimensions and almost into three depending on how you need it.
But here's the trick with PivotTables, something really important.
When I go back to my original data here, I'm going to go to cell I4 and I'm going to go set this GrossAmount here to a ridiculous number: 1000
10,000 whatever it is. And you'll notice this is in the 11:00 hour.
And we also notice that its food. So when I go back to Sheet2 and I look at food in the
11:00 hour my average is still $8.42. Nothing's changed.
And this is where PivotTables get their incredible speed as they work on an optimized cache. And in order to actually get your PivotTable to show
the current numbers you have to refresh the data every time it changes. So if you see if I go right click
and say Refresh so I've right clicked on my PivotTable. Click Refresh and now my numbers
get updated. So your PivotTable is not always in sync with the source data,
you always have to refresh it to make sure it's working from the most live copy because that's where they get their speed. So
that in a nutshell is how to create and modify a basic PivotTable and why they are so amazingly cool!