Creating Your First PivotTable

Quick reference

• 00:00 In this video, we're gonna show you how to create your very first pivot table,
• 00:06 and you're very quickly gonna see why this was one of my favorite features in Excel.
• 00:12 These things are amazing.
• 00:14 Check out the table of data that I've got sitting here.
• 00:17 This thing overall, if I press End+Down, You'll see that it's 2,500 rows of data,
• 00:22 and I want to get some key statistics out of this thing in a hurry.
• 00:26 So how do I go about doing that?
• 00:27 Do I write formulas?
• 00:28 Heck no, I'm gonna use this amazing tool called pivot tables.
• 00:32 Go back up to the top and you'll see that we've got some nice headers across
• 00:35 the top here, we've got a whole bunch of repeating data
• 00:38 broken down into different classes and categories, and stuff like that.
• 00:41 And now what we're gonna do is we're gonna perform some magic.
• 00:45 I'm gonna click somewhere in my table of data, anywhere.
• 00:48 And then I'm gonna go to the Insert tab and click Pivot Table.
• 00:52 When I do that you'll see that it's automatically selected my range of data.
• 00:56 And it's filled it in here for me.
• 00:57 I can see the marching ants around it.
• 00:59 And I get the option to place a new or existing worksheet.
• 01:02 I'm gonna choose New Worksheet for right now and say OK.
• 01:06 And this gives me a pivot table frame.
• 01:09 It also gives me the field well over on the right hand side here and all of these
• 01:13 headers that have come in, these were the headers on the previous worksheet.
• 01:16 You can see all the headers here have been pulled in as fields into my pivot table
• 01:21 field list.
• 01:22 Now what can I actually go about doing with this thing?
• 01:25 Well as it turns out, an awful lot.
• 01:27 What I'm going to do first is I'm going to take this class field and
• 01:31 I'm going to drag this guy down into the rose area a let it go.
• 01:35 And what you'll see is that my pivot table immediately populates with
• 01:38 every unique value that was in that particular column.
• 01:41 There's only two, Alcohol and Food.
• 01:43 Well that's kinda neat, 2,500 rows summarized just like that.
• 01:47 So let's see if we can break this down a little bit further now.
• 01:50 Let's grab these values in the Category field and we'll drag those and
• 01:54 release them right underneath the Class.
• 01:56 And you'll see that it actually nests these guys right underneath and
• 01:59 breaks down all the alcohol by the different subcategories of alcohol, and
• 02:02 all the food by the subcategories of food.
• 02:05 How cool is that?
• 02:06 Again, 2,500 rows.
• 02:08 You know what? What would be interesting next is
• 02:10 to know a little bit more about the units.
• 02:11 How many of these did we sell?
• 02:13 Well it happens that our field list, actually our original data table,
• 02:16 had a listing of the units by day.
• 02:18 So let's drop these in here too.
• 02:19 We'll put these ones in the VALUES area since they are values.
• 02:22 And immediately, it summarized all the values in that field as well.
• 02:26 How cool is that?
• 02:27 1,260 units of alcohol sales broken down by the different categories for
• 02:31 this entire period.
• 02:33 Well what if I want to go a little bit even more granular?
• 02:35 I'd like to see what hour these particular things were sold by.
• 02:38 I'll grab POSChitHour.
• 02:40 That's the point of sale chit hour and drop it into the columns area.
• 02:44 And look at that broken down by every hour.
• 02:47 In the 8 o'clock hour we sold no alcohol but we sure sold food.
• 02:50 And in the 2200 hour, which is 10 o'clock at night hour,
• 02:53 we sold lots of alcohol and food which is kind of interesting as well.
• 02:57 Now, this is great because I've pulled all these values onto the table in a way they
• 03:01 make sense.
• 03:01 But what if I didn't know, or I got something messed up?
• 03:03 What if I decided I didn't like this?
• 03:05 Maybe I originally thought, well, it'd be better if I saw a class up above POS hour.
• 03:11 I could put it below by letting go here and
• 03:14 it would like this which doesn't look like it makes a lot of sense.
• 03:17 Let me drag it up a bit and drop it above point of sale chit hour, and
• 03:21 now we can see that my hours are broken down.
• 03:23 I've got all of my alcohol sales by hour for all the different ones, and
• 03:25 my food sales by hour.
• 03:26 So that's quite interesting.
• 03:28 But you notice the key point here is that I don't have to be afraid to play
• 03:31 with this data.
• 03:32 It's like a great big Rubik's cube of data.
• 03:34 I can just move stuff around as I decide and think it might work best.
• 03:38 If I don't want this on the pivot table, I'll say pull Class right off,
• 03:41 drag it back to the field well, let it go, and it goes off the pivot table.
• 03:46 Or if I did want it again, drag it back down and put it above Category.
• 03:49 So really easy to slice and dice the data, it's really important.
• 03:54 Now, couple of key things you wanna be aware of here.
• 03:57 What happens if you lose this field well?
• 04:01 If we click the X it will go away.
• 04:03 So that's nice because we can see whats going on here.
• 04:05 But if we need to bring it back ever we just right-click on it and
• 04:09 say, Show Field List.
• 04:10 It will always hide when we click outside the pivot table.
• 04:13 And it should always come back when we click inside the pivot table
• 04:16 until we're done.
• 04:18 Then we dismiss it.
• 04:19 But then again, the problem is we click outside, we click back in, it goes away,
• 04:22 so let's bring it back by Show Field List off the right-click.
• 04:26 The other thing that's really important to be aware of is that not all
• 04:29 data in a pivot table is considered live.
• 04:32 And here's what I mean.
• 04:32 If we go back to Sheet1 and let's take a look at coffee here.
• 04:37 And I'm gonna make the sales for this item, ridiculously large.
• 04:40 I'm gonna say 1,000 units of coffee in the 10 o'clock hour.
• 04:44 It's a food and beverage, nonalcoholic beverage type thing.
• 04:47 Let's go see what happens with our pivot table now.
• 04:52 Nothing.
• 04:54 Here's the 10 o'clock hour.
• 04:55 Here's our non-alcoholic beverage, it's still 161.
• 04:59 Pivot table data's not live, it has to streamed into the pivot cache.
• 05:02 The way we do that is we right-click and we say Refresh and then it refreshes
• 05:06 the data from the original table and now you can see that we have all those items.
• 05:10 So this is the beginning, the introduction, the very first pivot table
• 05:14 you've ever built and these things are amazingly powerful.
• 05:17 There's a lot more to learn but this is the basics and you see why it's so
• 05:20 important to learn these incredible tools.

Lesson notes are only available for subscribers.