Creating Your First PivotTable

Quick reference

• 00:04 In this video, we're going to create our very first pivot table.
• 00:07 I'm going to show you why these things are so amazing and so easy to use.
• 00:11 We'll start by checking out the data.
• 00:12 I've got a big long list of food and beverage transactions here, and if I press
• 00:16 my end down arrow key, you'll notice that I have about 2,500 rows of data.
• 00:20 So if I want to go and summarize this, am I going to use formulas?
• 00:24 No way, that's way too much work.
• 00:26 We want to make this nice and easy.
• 00:28 So here's how this is going to start.
• 00:30 I'm going to click one cell somewhere inside my data range and
• 00:33 I'm going to go to Insert and choose Pivot Table.
• 00:37 At this point, it's going to go and select the data range for me and
• 00:40 then it will ask me where do I want to put it.
• 00:42 Do I want to put it on a new worksheet or an existing worksheet?
• 00:44 And this time, I'm going to choose New.
• 00:47 When I say OK, it creates the pivot table frame on the left hand side here, and
• 00:52 we have our field list on the right hand side.
• 00:54 And here's the cool thing about our pivot tables.
• 00:58 We can actually break this stuff down any way we want to see it.
• 01:01 So what I could do is I could say hey, if I want to summarize this quickly, maybe
• 01:06 I'm going to go grab Category and I'll drag this into this Rows area right here.
• 01:11 And when I let it go, you'll notice that it gives me a unique list of
• 01:14 every single category that I had in that dataset.
• 01:16 2,500 rows now summarize all the way down to about 14 which is pretty cool.
• 01:21 Maybe I want to analyze my units.
• 01:23 I'm going to grab Units, left-click and drag and I'm going to drop it
• 01:26 into the Values area here because let's face it, units are values.
• 01:30 And when I let it go, it now goes and counts all of those units up nice and
• 01:34 quick, which is pretty cool as well.
• 01:37 Maybe I want to do some more breakdown, maybe I want to see it alcohol and
• 01:40 food broken down.
• 01:41 I'll grab Class, I'm going to left-click, drag it down and
• 01:45 I'm going to drop it right here above Category on Rows.
• 01:48 And just like that,
• 01:50 we've now sub-categorized our categories by the actual class themselves.
• 01:55 What if I want to do more?
• 01:56 What if I want to break this down by say hour?
• 01:59 Well, no problem.
• 02:00 Let me just go and find the field, here it is POS Chit Hour for
• 02:03 point to sale Chit Hour.
• 02:05 I'm going to drag it into Columns and let it go.
• 02:09 And just like that, we can actually now see our sales by hour analysis.
• 02:13 So you'll notice that 8 o'clock, 9 o'clock, 10 o'clock hours,
• 02:16 we sold no liquor but we sure sold a lot of food.
• 02:19 So that's pretty cool.
• 02:20 And then starting at 11, that must be when we start selling our alcohol for
• 02:24 the day all the way up to 2,200 hours.
• 02:26 Now, what if I decide that I didn't like this?
• 02:30 Well, this is the cool thing about pivot tables,
• 02:32 they're just like a big Rubik's cube of data.
• 02:34 All I need to do is just move a field from somewhere else.
• 02:37 So I could say class, I don't really want to see class on rows,
• 02:40 why don't I grab it left click and drag?
• 02:42 And I'll just drop it right over here under the Point of Sale Chit Hour.
• 02:46 And at that point, I've now got my food and my alcohol broken down by every hour.
• 02:51 And I think well, it is what I asked it to do, but
• 02:53 it's not really what I want to see, so let's try again.
• 02:57 Let's grab Class, and move it above the hour.
• 03:01 And just like that, it re-slices it so that now I've got all of my alcohol
• 03:05 sales broken down by hour, and my food sales down by hour.
• 03:08 And then I might look at this think, no,
• 03:10 that's not really what I want to see either.
• 03:13 I just don't want class on there at all.
• 03:15 So I'll grab it, left-click, drag it back to the field list and let go.
• 03:21 And it takes it off the PivotTable.
• 03:23 And then I might think, but wait, I really did want it but I want it back on rows.
• 03:28 So I drag it back down and drop it above Category.
• 03:31 And away we go, it's back on the Pivot Table.
• 03:33 This is the cool thing about pivot tables is that they work very,
• 03:36 very quickly to take the data.
• 03:38 It's all pre-sliced in this thing called the pivot cache.
• 03:40 So that when you drag and drop fields anywhere you want,
• 03:43 it actually summarizes things up nicely and happens super, super quick.
• 03:46 From 9 rows of data, to 90,000 rows of data,
• 03:49 to 900,000 rows of data, no big deal.
• 03:51 It's a fantastic tool for this.
• 03:53 But one thing we do need to know about pivot tables is that the data is not
• 03:57 connected live.
• 03:58 In order to get this kind of reaction here,
• 04:00 it actually streams into this offline thing called the pivot cache.
• 04:03 And I can actually demonstrate that for you.
• 04:05 If we come up back to Sheet1 and I say hey, you know what,
• 04:08 let's take a look at these coffees right here.
• 04:10 So I've got a few of these.
• 04:12 I'm going to go and
• 04:12 sell something ridiculous like 100,000 units of coffee here.
• 04:16 And you think this should be pretty easy to see, it's in the 2,200 hours.
• 04:20 It's in the non alcoholic beverage category.
• 04:22 So when I run back over and I look at 2,200 hours and
• 04:25 I come on to non-alcoholic beverages, I still got 79 units, what's going on?
• 04:30 And this is the key thing is that Pivot Tables are not
• 04:32 connected live to your data.
• 04:34 You do need to refresh them.
• 04:36 So an easy way to do that, right click on the Pivot Table and say Refresh.
• 04:41 And now you can see that it shows up with these guys.
• 04:44 I'm going to go back and we're going to change this back to two units.
• 04:47 I'm going to show you another way.
• 04:49 When we come over to Sheet2, I can also go to data Refresh All and
• 04:54 that will also refresh the pivot table.
• 04:57 So that's the very basics of working with this and
• 04:59 start to expose some of the underlying power of what we have here.
• 05:02 And for the rest of the course, we're going to look at the amazing things we can
• 05:06 do to format and make pivot tables really sing.

Lesson notes are only available for subscribers.