Introduction to PivotTables - Microsoft Excel Tutorial

(bright music) (gentle music) - Well, here it is, everything you ever wanted to know or at least thought you wanted to know about pivot tables. Now, I acknowledge that many of you are going to arrive upon this lesson and you will have already done pivot tables. So I give you some value, all right? One of the things I want to do here is at least give you some theory behind it because you may have to explain what a pivot table is to someone else, and this is going to help you out a lot. So I'm going to give you a little bit of theory and I think this is, if you love data, you're going to be really into it, okay. And the other thing is, if you're new to it or if you've really just, even if you've done it and you've done a lot of experimentation, what I really want to do is kind of get to the mechanics, right? So I always think like theory plus technical equals success, right? So we're going to hit both of those in this pivot table section. Okay, so let's think about theory real quick. What is pivot table theory, what does that even mean? Okay, so on one level, and you can use this to impress your boss and your manager, okay? Pivot tables satisfy... Am I going to be able to spell satisfy? I think I will. Satisfy the need. The suspense is killing me here. For what, for what, right? All right, so the answer is descriptive. Now, you could say statistics or analytics. I'm going to say analytics because a descriptive statistic is part of what a pivot table does, all right? But this is it, because though I am generally against saying that, well, you know, the answer to what descriptive analytics is, is a technology. Like, obviously that's not the case. What I do want us to think about is the workflow, why do pivot tables exist? And that is because they exist to do descriptive analytics. So if you're good at pivot tables or if you want to be good at pivot tables, you also get to say that like, "Hey, I'm good at descriptive analytics," right? Okay. So in thinking about descriptive analytics, what are they? Okay, so on the one hand, you know, 'cause I teach a descriptive analytics certificate program for Wake Forest, right? So my view of descriptive analytics I just said, generally the people who work in descriptive analytics are people are like, here's the data, that's what your boss has said. Like, here's the data, you know, do something with it. What are you going to do with it, okay? So you can do what they call exploratory data analysis. But eventually, right, eventually you're going to have to come up with some sort of summary, okay? And this process, all right, this process here is descriptive analytics, okay? Because the summary is going to contain not all the data, but it's going to contain components. Things like sums, things like averages, things like minimums, maximums, counts, you know. Even though the complication to get to some of these things, like it could be complicated to get a counting. Your data may not be easily counted, right? But generally, once you get to this point, right, you're able to report this stuff to a manager, to a stakeholder. All right, so that's what pivot tables allow us to do in the abstract. But if we think about the mechanics of a pivot table, what it's actually doing and why it has to do it, let us actually consider another data set which I am going to now awkwardly make up on the spot. So it's going to look like this, but don't worry, I've done this live before, so I feel like I should be able to do this recorded, all right? So, you know, we have transactions coming in, over here is not a key column, let us say that this is not a key column. So that means I could have a repeat. So, you know, I'm one of these salespeople, and each one of these is a record like that, okay? So now I'm going to type in quantity, so this will be a quantity of what we sold. So maybe I'll do two here, three here. What I'm reminding myself is don't make these numbers too high 'cause I got to add 'em up. All right. So we'll do a five here, two, one, and nine. Okay. That's almost getting too high, but we're going to allow it. And then maybe, you know, the products that we sell have some sort of category associated with them, right? What kind of categories could they have? Well, they could have high, high, you know, medium, high, low, medium, something like that. All right. So you get a data set that looks like this, something like this, you know, maybe there's other parts of it that we're not going to deal with, right? But let's say there's other fields out here, right? All right, so just put on your imagination, there's other fields out here, we're just going to stick to the relevant ones here, all right? So you get a dataset that looks like this. Now, in a pivot table, all right? What a pivot table does is it provides us the ability to summarize information through aggregation, okay? So this sigma here, I know it means sum, but for our purposes we're going to refer to it as aggregation. Anytime I have a sigma here, we're going to think aggregation. And aggregation is just like the descriptive statistics. Sum, you know, min, max, et cetera, all right? So whenever I'm thinking about this sigma here, and this isn't just me, this is also Excel. So Excel is going to use this symbol to refer to the process that I'm about to show you. Okay. So let's say you want to actually take this information here because it's a lot, right? And imagine this goes down forever but we're only going to deal with this part here, right? And you want to summarize it because someone can't look at this and figure out what are the important key details of it, right? So what you could do is on your pivot table, you're going to drop in your name column over here. So let's imagine we took name and we put it over here. Well, what happens in Excel's brain? Well, the step one on a pivot table, which it always does is when you drop something like that into the column over here, in a row column the first thing it does is it uniqueifies it. So that's not a J at all. Put a J there. And I'm going to put an M, right? Because we're going to uniqueify this list, okay? So I dropped name in there, okay? So then I want to understand, well, what's the quantity? Okay, so I could drop quantity up top here, I could do that. And what's going to happen, right? Well, here's the thing. We have a problem here, because once I put this in here, right, it uniqueified the name list. So if I go back here, we got two of these, we got two of these. These are easy, right? So what I need to do is some way I need to squash it down, right? I need to take these numbers here and flatten them down into one number that can be reported so that I satisfy the rules, all right? So how could you do that? Enter the aggregation, right? So what aggregation does is it takes 10, you know, I'm just going to put a bunch of random numbers here, right? It takes all of them like that and it squeezes out just one number. It's pretty cool, right? And so that's what we need to do and that's what satisfies descriptive statistics. So what are ways we could turn all of these down into one number? Well, we have a lot of choices, right? What are my choices? Sum, min, max, count. Let's say we do sum, all right, we're going to do a sum here. And so we do a sum, so two plus three is five, right? Five plus two is seven. And this is it, this is all a pivot table does, all right? One and nine. So then if I want to do categories over here, right? I can't really sum these up because they don't have a, you know, they're not a type that you can do that with, they're a text type. So what I have available here is count. So I can just drop that in and because it's text, if you have the latest version of Excel, it's smart enough to know that the default here is going to be to count it. So let's see here, oh, if I drop categories, see, because categories is different I can't just drop it in there. So (laughs) already I've overthought my idea. I'll show you how all this works when we jump to the Excel file. But for now, before you leave, I just want to give you one last final thought, okay? So if you have done, like I said, you may have done pivot tables before, right? So it isn't just descriptive analytics. But the big thing here, okay, so if you get a handle on the mechanics and the theory, okay? All of this here, what you learn here, this is going to be the backbone for Power BI. So if you really have not put your head around pivot tables yet and you want to learn Power BI, you should do that, because once you get a handle on pivot tables, it's such an easy move right into Power BI.