What is a PivotTable - and Why Do You Care?

Quick reference

• 00:04 In this course, we're going to look at one of the most amazing tools that exists
• 00:08 inside Excel called the pivot table.
• 00:10 If you've never used a pivot table before, you may be wondering
• 00:13 what the heck it does or why do you even care about these things, and
• 00:16 lets be honest, the name is not the most engaging to really draw you in and
• 00:20 make you understand what it's all about.
• 00:21 But I'm gonna blow your mind in the next few minutes here as I show you what
• 00:25 a pivot table can actually.
• 00:27 You'll notice that I have a table of data sitting in front of me here.
• 00:30 It's a whole bunch of point of sale chip data, and
• 00:33 what I'm gonna do is I'm just gonna go and open up the filter and
• 00:36 show you that there's five years of data in here.
• 00:39 We can see that we're starting with 2013, and if I go end and down,
• 00:43 there's approximately 349,000 records in this data set, and they're obviously
• 00:48 not in order if I've got 2012 data at the bottom and 2013 data at the top,
• 00:53 and we had data from different years like 2009 as well as you can see.
• 00:58 Now what if I told you I'd like you to summarize all these records and I'd like
• 01:02 to know how much we have in the way of sales for food and for alcohol for each
• 01:07 of the five years and what those actually equate to as a percentage of total sales?
• 01:11 If you've never used a pivot table,
• 01:13 you'll be looking at this going holy cow that's gonna take me forever.
• 01:16 So watch how quickly we can actually do this with a pivot.
• 01:19 This is why you care.
• 01:20 We're gonna to inset we're gonna go to pivot table and we're gonna
• 01:24 choose a new worksheet and land a pivot table in a new worksheet ready to go.
• 01:29 And you'll see it'll do just a little bit of work here.
• 01:30 And at this point we an start building a pivot table for our needs.
• 01:35 I'm gonna go through this relatively quickly because what I'd like you to see
• 01:38 is I'd like you to see the power of what we're actually dealing with here,
• 01:41 rather than learn how to do that,
• 01:43 because that's what the focus of course will be all about.
• 01:46 So we're gonna put our dates on the left hand side.
• 01:49 We're gonna go and we're gonna put our category class across the top, and
• 01:53 we're gonna throw our amounts into the values area.
• 01:55 And I'm gonna actually do that twice.
• 01:57 I'm going to right click on this guy here and format it.
• 02:02 We'll go to a number format and we'll make this a nice accounting with no
• 02:08 decimals and no symbols, and we will change the name on this
• 02:13 one to something a little bit nicer, and say okay.
• 02:17 So that looks nice.
• 02:18 We've got some good values there.
• 02:20 We'll also go and change this guy here a little bit.
• 02:23 We'll go to say, percentage of column total.
• 02:26 That looks good.
• 02:27 And we will call this one percent of Total.
• 02:31 And say okay.
• 02:32 We've now got that report that I was already looking for
• 02:36 that summarized sales by year and percentage.
• 02:38 But we can even do a little bit better than this.
• 02:41 Let's go ahead and add another column here.
• 02:43 We'll make this a little bit taller.
• 02:45 And I'm gonna go in and add some tools so that users can actually slice
• 02:49 through this stuff and take a look at the different pieces on their own.
• 02:55 So we can go and we can put a class over here,
• 02:59 a nice little slicer, and some categories down the left-hand side here,
• 03:04 and maybe we'll just go and make a quick manipulation on this one as well.
• 03:08 We'll go and put that at the top.
• 03:11 I can make this a little bit shorter,
• 03:13 just to make it look a little bit more attractive.
• 03:15 Give it a couple columns and
• 03:17 we'll remove some other different pieces that we don't actually need in here.
• 03:23 So how long did that take?
• 03:24 About a minute, and now we've got the ability to drill in and
• 03:28 take a look at individual pieces from our data.
• 03:30 We can summarize it up and just look at breakfast items or just look at canned
• 03:34 beer items, or we could even look at a couple of different things.
• 03:37 Maybe we wanna see say, burgers and
• 03:40 our draft beer together because we put those on as specials all the time.
• 03:44 This is the incredible power of a pivot table and we're gonna teach you how to do
• 03:48 all of this and a ton more stuff inside this course.
• 03:51 If you've never used pivot tables,
• 03:53 they are mind blowing tools for very quick analysis and drilling in.
• 03:57 You have to learn about these babies.

Lesson notes are only available for subscribers.