What is a PivotTable - and Why Do You Care?

Quick reference

• 00:04 In this course we're going to look at the pivot table,
• 00:06 one of the most amazing tools that actually exists inside the Excel product.
• 00:11 Now if you've never used a pivot table before you might be thinking,
• 00:13 what the heck is that?
• 00:14 Why do I even care?
• 00:16 And let's be honest, it's not the most enticing feature name ever and
• 00:19 it's not really very descriptive either.
• 00:22 So let's take a look in this video at what they do and
• 00:25 why we want to explore them further.
• 00:27 Why are they so amazing?
• 00:29 In this case here I've got a nice big table of data here that breaks down food
• 00:32 and alcohol sales for a restaurant company.
• 00:36 Now, our point of sale chit date here, you'll notice it goes and
• 00:39 spans over from 2013 all the way back to 2009.
• 00:43 How many records do we have?
• 00:44 Well if I press End, down arrow,
• 00:46 you can see that we've got about 349,000 records in this data set.
• 00:50 And they're obviously not in order because we started with 2013 and
• 00:53 got to 2012, that's where the break is, but
• 00:57 2009 is mixed up in all the middle of the stuff as well.
• 01:00 Now the question that I've got for you is, if I asked you to quickly summarize this
• 01:04 by year, break it down between food and alcohol and give me both the total sales
• 01:08 and the percentage of total sales for each of those things, how fast could you do it?
• 01:12 And the reality is if we tried to write a bunch of sum if formulas to do that that's
• 01:16 going to be really hard.
• 01:18 It's going to take a lot of time, it's not going to be very robust.
• 01:20 So let's look at what the pivot table actually adds for us in this video.
• 01:23 I can click somewhere inside my table here and say insert pivot table.
• 01:29 This will create a new pivot table for me on a new worksheet,
• 01:32 which I can then start configuring it to see how I want everything to look.
• 01:36 I'm going to grab my date field, drag it on to Rows.
• 01:39 And you'll notice that groups it up for me nicely by year right away.
• 01:43 I can then come down here and say, hey, I want to see my amount.
• 01:46 So I'll drag my amount into the Values area.
• 01:48 And just like that I have a total breakdown by year for my amount.
• 01:53 I'm going to throw Class onto my Columns.
• 01:55 And there we go, it's now broken down by food and alcohol.
• 01:58 And I'm even going to go and grab Amount and
• 02:00 throw it onto the pivot table a second time.
• 02:03 Why a second time?
• 02:04 Well because now I can actually start playing around with these things.
• 02:07 This one is going to be my Sales $.
• 02:10 And I'm going to quickly change the format of this guy here so
• 02:13 that it looks a little bit better.
• 02:15 So we'll set this up with a number, with thousand separators,
• 02:17 and I'm going to get rid of all the decimals.
• 02:20 Now I'm going to do the same thing over here, except that I'm going to change
• 02:24 this to summarize and show my values as a percentage of a column total.
• 02:29 Just like that, I have my percentage.
• 02:31 So I can now come back and say, this is the percentage of total.
• 02:36 Now, just like that we've already broken everything down.
• 02:39 But what if I wanted to get even more in-depth with this?
• 02:42 I'm going to insert a new column on the right hand side here.
• 02:45 We'll just open that up row 1, make it a little bit bigger, and
• 02:48 I'm still going to open up row one to make that a little bit taller as well.
• 02:51 I'll click inside my pivot table, and I'm going to go up to Pivot Table Analyse.
• 02:56 And at this point I'm going to go down insert a new slicer.
• 02:59 My slicer is going to be for Class, and another one for my Category.
• 03:03 And just like this, we'll just snap this one into the grid and resize it.
• 03:10 And we'll move Category over the same way.
• 03:12 Snap it there and resize it a little bit.
• 03:15 And now what you can see is that I can drill into my Food,
• 03:18 can drill into my Alcohol.
• 03:20 I could go and say, I'd really like to see draught beer, for example.
• 03:24 We'll clear this filter here.
• 03:25 Notice that it's changing things all the way along.
• 03:28 And maybe I want to see my burgers and beer together.
• 03:30 And just like that, my food now color popping back in place.
• 03:34 But further than that, I can even do more.
• 03:36 I can come back and say once again we'll go to pivot table tools analyze,
• 03:39 insert timeline, and we'll add one for the point of sale chit date.
• 03:44 Just like this, I can now snap him up to the grid as well, we'll make him snap
• 03:48 nicely in place, make this a little wider, change this guy up to be years.
• 03:53 And now you can see I can break it down to say, just show me the 2010 sales, or
• 03:56 the 2011.
• 03:57 Or maybe I want to see three individual years.
• 04:01 This is why pivot tables matter to you and why you should care.
• 04:04 Really, really quick ways to break down data and actually go and slice and
• 04:08 dice it the way you want to see it.

Lesson notes are only available for subscribers.