Creating PivotTables

Quick reference

• 00:04 Now we're going to talk about one of the coolest features that exists inside Excel; the PivotTable.
• 00:08 I'm not going to tell you a whole bunch of stuff about what PivotTables do because
• 00:11 it's a heck of a lot easier to actually show you. But before we get
• 00:14 to that we need to just understand some characteristics about the data that we have to work with that
• 00:18 actually build into good PivotTables. And basically
• 00:21 part of the wording in this thing is PivotTable so that kind of leads you to
• 00:26 believe that you need a table to start with. And effectively that's where we are here.
• 00:29 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.
• 00:36 You'll notice that it is actually formatted as a table. If you were to
• 00:39 scroll through the whole list you'll see that there's no blank rows or no blank columns
• 00:43 that are separating this data up. So it's a good solid contiguous block of data and that
• 00:47 is the number one ingredient for having a good data source for a PivotTable.
• 00:52 The second thing is to make sure that we have a good header row across the top that describes our columns really well.
• 01:00 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
• 01:03 so awesome. It's real easy we click somewhere inside our
• 01:07 list of data here. We go to Insert and we click PivotTable.
• 01:11 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.
• 01:18 What we get is we get a PivotTable frame and here's how PivotTables work:
• 01:23 What we can do is we can scroll down and say you know what, I'm going to grab Units here and
• 01:26 I'm going to drag it into the values field. I'm going to let it go.
• 01:30 And it's just summarized my 772 records I believe, to say that there's 782 units
• 01:35 total inside those 772 records just like that! And this is cool because
• 01:40 700 records, 700,000 records, it doesn't matter it goes almost as fast. Now
• 01:46 what else can we do with this? Well lets go grab the POSReportingGroupCode and throw that on the Rows field.
• 01:51 Oh okay now we get a breakdown between alcohol, food and non-alcoholic beverage.
• 01:56 We can further break that down by grabbing a category description and dragging that underneath the reporting groups.
• 02:02 And there we go now we can see what the breakdown of these particular items is between our alcohol, food or non-alcoholic beverage.
• 02:09 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
• 02:16 we drag our fields on into the different areas on the PivotTable grid.
• 02:21 What about modifying it? Well, that's easy too. We just grab these guys here;
• 02:26 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
• 02:30 and it takes it off the PivotTable. You can see the pivot updates.
• 02:33 We'll remove Category as well, drag that back up top. So now we know just what the values are we have in there.
• 02:39 And now let's go and throw the GrossAmount down in here. So now we're looking at dollars.
• 02:44 There we go, we've got $6000 worth of sales on this particular day.
• 02:49 What else could we do? Well you know what, we could take our POSReportingGroup and instead of having it down
• 02:53 the left hand side what if we did this? Lets drag it and put it into the Columns field.
• 02:58 So now the PivotTable reshapes and it's telling us that we still have the same
• 03:02 values they just kind of turned on their ear a little bit. What else can we do with this?
• 03:06 Well lets grab the Hours field. We'll drag that into Rows
• 03:11 and now I can actually see what my sales were by product type, by hour, which is pretty cool.
• 03:19 I can also go further with this. I don't have to look at just sums of values. I can actually go right click
• 03:25 say Value Field Settings
• 03:27 and I can go and choose to have an Average. So lets see what the average of the sale is.
• 03:33 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
• 03:40 and I'm just going to change this to Accounting. Just so we get something consistent and say OK.
• 03:45 And here we go now we've got some sales. So we can see that
• 03:48 our average sale of alcohol is pretty low in the morning which you'd
• 03:51 expect because most people don't drink early in the morning. It generally stays the
• 03:55 same to around 6:00 at night and then we have a big spike. I guess people come in
• 03:58 for dinner and they want to have a beer or something that's what ends up happening there. That's kind of neat.
• 04:02 Our food sales also spike in the dinner hours compared to what
• 04:05 we have, except for it looks like we have breakfast at this particular restaurant.
• 04:09 Some neat things, you can see that you can actually get into here.
• 04:12 This is where PivotTables are so cool because you can drag these fields around from
• 04:15 one place to another and get them into the places that you want to show the data
• 04:20 from two dimensions and almost into three depending on how you need it.
• 04:25 But here's the trick with PivotTables, something really important.
• 04:28 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
• 04:37 10,000 whatever it is. And you'll notice this is in the 11:00 hour.
• 04:42 And we also notice that its food. So when I go back to Sheet2 and I look at food in the
• 04:47 11:00 hour my average is still $8.42. Nothing's changed.
• 04:53 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
• 05:01 the current numbers you have to refresh the data every time it changes. So if you see if I go right click
• 05:06 and say Refresh so I've right clicked on my PivotTable. Click Refresh and now my numbers
• 05:12 get updated. So your PivotTable is not always in sync with the source data,
• 05:17 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
• 05:23 that in a nutshell is how to create and modify a basic PivotTable and why they are so amazingly cool!

Lesson notes are only available for subscribers.