Creating PivotTables

Quick reference

• 00:04 In this video we're going to look at one of the most incredibly cool tools inside
• 00:07 Excel called the PivotTable.
• 00:09 Now these things have a bit of an intimidating name, but they shouldn't.
• 00:12 They are absolutely fantastic.
• 00:13 And they are the quickest way that we can actually go and summarize, and slice and
• 00:17 dice data into a variety of different formats.
• 00:20 It kind of allows us to create kind of a Rubik's Cube of our data,
• 00:23 which is really kind of neat.
• 00:25 So in this case here, we have a nice set of data here.
• 00:28 And some of the key characteristics that we always wanna work with when we're
• 00:31 working with a PivotTable, is we'd like to have our data in a good tabular format.
• 00:35 So again, that means we need a header row,
• 00:37 we need consistent types of data down the columns, no blank rows in between.
• 00:42 And the ideal way to actually work with your PivotTable is actually
• 00:46 have your data in a proper Excel table to begin with.
• 00:49 So I' gonna go and set up an Excel table right now.
• 00:52 And we'll just call this particular table here,
• 00:56 how about something like, pet inventory.
• 01:01 Now in order to create a PivotTable, what we're gonna do is we're gonna click inside
• 01:05 a cell, we're gonna go to Insert, and we're gonna choose PivotTable.
• 01:09 And it'll ask us to select the table or range.
• 01:12 You'll notice it's picked up the name of the table that I created already.
• 01:15 We could use an external data source like a database as well.
• 01:19 And I'm gonna say that I'm gonna put this on an existing worksheet, and
• 01:22 what I'm gonna do is I'm gonna click the little down in the corner here,
• 01:27 I'll go and drop it into the example PivotTable page, and we'll say OK.
• 01:32 And what you'll see is you get this PivotTable frame that ends up
• 01:35 showing up here.
• 01:37 Now, the nice piece about a PivotTable is we also get this little area over here
• 01:41 that is our PivotTable builder.
• 01:44 So what we can do is we can basically just drag an item from the field list up top,
• 01:50 and let it go in say the rows area.
• 01:52 And it will create us a unique list of every value that's in that PivotTable.
• 01:57 So it just cuts it down to a unique set.
• 02:00 So what if we said, well you know what maybe I don't want dates,
• 02:04 I'm gonna pull this back off the PivotTable and I'll let it go.
• 02:06 So if I decide that I don't like the way it's going, I can change it.
• 02:10 I could grab my inventory and pull it down onto rows, and there you go.
• 02:14 You can see all the unique inventory items that we started
• 02:17 off with in our original table.
• 02:19 I could pull say, sold by,
• 02:21 and I could start figuring out who sold the adorable kitty cats.
• 02:26 We've got a Fred, Jean, John and Mary have done so.
• 02:29 I could then go and take, let me see, how about price and
• 02:33 put it into the values area.
• 02:35 Well, we'll just drag that down in here and let go.
• 02:37 and you'll notice that it very quickly goes through and it actually sums up All
• 02:42 of the adorable kitty cats Fred has sold, and he sold $175 of adorable kitty cats.
• 02:48 Well, what if I don't want it to look exactly like this?
• 02:51 What if I wanted my Sold By to be sliced across the top here,
• 02:54 so I saw all of my inventory items down the left, and
• 02:57 all of my salespeople across the middle, and then a big table there.
• 03:00 Well no problem.
• 03:01 We'll just grab Sold By and we'll drag it over into the Columns area, and
• 03:06 there we go, that's exactly what we've got right now.
• 03:08 So this is the beautiful thing about PivotTable is it's very quick
• 03:13 to change the way that you look at data.
• 03:15 Don't like where it is?
• 03:17 Drag the field off the PivotTable and let it go.
• 03:19 Maybe we don't wanna look at price, we wanna look at say Commission.
• 03:22 So, I'll just get rid of price and I'll put commission on here.
• 03:25 Of course, if I wanted I could also even have price too.
• 03:28 I could drag that in and then have both pieces.
• 03:30 Okay, so here's the sum of commission and the sum of price.
• 03:33 Really, versatile tool this and
• 03:35 so quick, but there is one thing I'm just going to drag this back in here.
• 03:40 There's one thing you need to know about PivotTables.
• 03:43 I'm gonna dismiss the field list right and get it to go away.
• 03:46 And we can see that we've got our values here.
• 03:48 I'm just gonna go back over to the example data set for a second, and
• 03:52 I'm gonna change this commission up here to something ridiculous.
• 03:55 so we're gonna go to $100,000, so this is Fred's lovable kittens that he sold here.
• 04:01 And we'll go back to the PivotTable, and we'll look up lovable kittens and
• 04:06 here they are, and here's Fred and you can see that.
• 04:09 That sum of commission has not changed.
• 04:12 You go well why is that.
• 04:14 Well here's the thing, because PivotTables need to be so
• 04:17 quick they actually preprocess data when we use them.
• 04:20 So the data is not actually connected live to the data set.
• 04:24 In order to actually get these to work what we need to do is we need to
• 04:27 right click on our PivotTable.
• 04:28 And say refresh data, and at that point it will go and it will stream all of
• 04:33 the data from the original table into the pivot cache which serves up the PivotTable.
• 04:39 So again if we were to go back and saw well that's not right,
• 04:42 let's go put this back to the $1.35 that it was.
• 04:47 and we go back over to our PivotTable, you'll see that it hasn't changed,
• 04:51 again we need to right click and we need to say Refresh Data.
• 04:55 So this is one of the big key points to remember.
• 04:58 These are extremely quick to refresh data.
• 05:01 The drag and drop interface is awesome, and
• 05:03 lets you look at data in all kinds of different ways.
• 05:04 They're super easy to use, but they're not live.
• 05:08 You have to right-click and refresh, when you want your PivotTable to update.

Lesson notes are only available for subscribers.