Creating PivotTables

- One of Excel's most creative and powerful tools is called a PivotTable. It's a separate entity that you can create based on data. Typically, the data you use as the source for PivotTable is something that's organized like a list, perhaps like what we're seeing here on the screen. If you're trying to analyze this information, you simply have some tools available on the Data tab. Now most of them don't let you analyze the data so much, but at least allow you to view it in different ways. This list consists of about 900 rows, information about salespeople who sell certain Products to different Customers. They can sell to different Regions. We're tracking information over a two-year period. You see the various column headings here. Now, how do you make sense of this? On the Data tab, there's certainly Sorting. This could certainly help if we wanted to focus on the Products in order, or the Salespersons. Sorting, of course, gives us all kinds of capability for seeing the data in a more organized way, but no real analytical capability is implied there. A Filter certainly helps. We can look at just the data we're most interested in for the moment. Subtotal gets a little bit closer, maybe, to what we might want to be doing in terms of coming up with some analysis and totals based on a sorted list, maybe totals by Region, by Product, by Customer, and so on, but trying to make sense out of this in other respects isn't so easy in Excel. Now, one way is to write some formulas off to the right of column H, and I'm making column I a lot narrower now. We've got a table over here. I'll scroll a little bit to the right here. Here's what we might be interested in. We've got a list of all of our salespeople and the various Items that we sell, and we're simply trying to find out how many Items each person has sold. Now this is not a PivotTable, but we can create something like this by way of a PivotTable in short order. Look at some of the formulas in here. They're pretty much all the same. I'll double-click the one in K13. Not exactly the easiest formula to handle. I wouldn't call it rocket science or anything like that, but a good experienced Excel user does spend a little bit of time on this, maybe take 10 minutes or so, to not only get the formulas down but to ensure that they are correct. If you were making a presentation of this information, too, how easy would it be to change that top row so that we could focus on how many Items each of our Salespersons has sold to each Customer. In other words, could we see the Customers across the top? Could we make that switch quickly and easily? Well, if you were the analyst, you might say, "Well, I'll get back to you in about 10 minutes or so, "we'll have that ready for you." With PivotTable, we could create a list like this in about five or 10 seconds, and we will be doing this momentarily, but we need to talk a little bit more about what a PivotTable is, how we create it, and also about our source data. You can't just say, "I've got a bunch of data, "therefore, I've heard so much "about this PivotTable feature, "I'll just see what PivotTable can do with it." The data that's going to be the source for your PivotTable has to be organized as a list. Now possibly, you've converted it into a table. That's not a prerequisite, although it could be helpful. The list that we're looking at here must not have any empty rows within it, nor empty columns. Your headings should be in a single row up top, and it's generally good advice for any list you'll be working with in Excel, anytime you do Sorting, or Filtering, or using the Subtotal feature, having your list organized like this makes good sense. If you have any interim Subtotals in here, get rid of them. Get rid of those empty rows. If you haven't seen the list in a while, click within it, press ctrl + a, then press ctrl + . four or five times. This will move you around the corners of the range. When you get to the bottom, if you haven't seen this in a while, scroll just a little bit more just to make sure you're not missing any data below. Keep pressing ctrl + . to take us right back up top. So we've scoped out this list. If you work with the data often, you don't have to do that very often, of course, you would know that this list is together as a unit. So what can we do next with PivotTable? After pointing you to Sort, and Filter, and Subtotal and reminding you of those capabilities, you certainly would think that PivotTable is on this same Data tab in the Ribbon menu system, but it's not, it's on the Insert tab. There is a feature which we'll look at, perhaps later, Recommended PivotTables, but let's just jump right into the idea that we'd like to see a PivotTable, and maybe what we'd like to do is to create a PivotTable looking like what we're seeing off to the right here, this list. Quick look at that, by the way, the total is 7997, and you can see who the best performer is, at least in terms of Items sold. Here he is right here, Sam Ramey, over a thousand, the only one over a thousand. So we're expecting to see this list pretty soon. Now, of course, I do need to talk about it as we do this so it will take a bit longer than 10 seconds. So, once we've recognized that our data is in order, and by the way, you might have empty cells in here. That probably isn't what you want, but that's not going to stop us from creating a PivotTable. Just make sure we don't have any empty cells all the way across, and we don't in this example. Click on any cell within here. On the Insert tab, the leftmost button. Many times, you'll simply click that button, and then click OK. The default Location for a PivotTable will be on a New Worksheet, and that's what we're going to do the first time around. Usually, you want to give a quick glance here at the Table Range. Has Excel figured out the extent of your data? It certainly has here. It's saying I see data from cell A1 in the upper left hand corner down to cell H910 in the lower right. We'll click OK. We're about to see a new worksheet, with a PivotTable placeholder, ready for us to create a PivotTable. Placeholder over on the left hand side. On the right side, you'll see a dialog box, PivotTable Fields. Now it might be docked or anchored on the right side and that's fine, you can leave it there. If you wish, you can click and drag its title bar, drag it out here, you could possibly resize it. Now, you don't really have any rationale for doing this just yet, but as you work with PivotTables, sometimes it's handy to have this closer to the data, but you can move this around a little bit if you wish. If you want it to go back to its docked position, simply double click its title bar, and for a while, I'll just leave it there. There we are. We see in the top portion here, all of the field names that we saw earlier. Now, all I have to do to get this PivotTable is first click Salesperson, and two things are going to happen on the screen. Far left, we see an alphabetical list of all of our salespeople. Down below the PivotTable Field list here, is a section called ROWS. Sometimes these are referred to as Row Labels, and over in the PivotTable, which for the moment has no numbers, we see that term Row Labels as well. I'm now going to click the box for No. of Items. We're tracking No. of Items sold. That's a numeric field that will automatically appear in the PivotTable, and we see it off to the left, and also below here, in the section called the VALUES area. The wording might seem a little strange, but here are those Totals. Remember the 7997 that we saw? Then you can see who our best Salesperson is, it's Sam Ramey, right here. Third thing we need to do is a drag. Simply drag whatever field we want into the COLUMNS area, and so if we want to see the Products, we'll drag Product into the COLUMNS area, and there's our PivotTable. If we go back to the actual worksheet that we started with here, the Creating worksheet, we see off to the right, ultimately, the same numbers. Of course, you're not going to remember them all, but take Dotty Kirsten here, 209, 118. Jump back over, here we are, 209, 118, and all the other numbers are accurate, too. We've got a PivotTable, and I think you could've imagined that happening even faster had I not been explaining and talking about it along the way. Easy to get to. We've got a PivotTable, and need I mention, those long formulas that we saw earlier, not a formula in sight. Not only no formulas here, but no formulas along the Grand Totals either. PivotTable has done this in the background for us. We've got a PivotTable. One suggestion I usually make with PivotTables and what I usually do myself when I create a PivotTable, the terms Column Labels and Row Labels, although valuable, and those are terms you want to get familiar with, don't necessarily need to be here, but let me do point out, as we look at the PivotTable Field list to the right, and I'm going to move the upper part this way so we can see a little bit better, this lower portion of the PivotTable is really viable. It reminds us of the layout of the PivotTable, and the box here that says ROWS, in the PivotTable itself we see Row Labels. ROWS, or Row Labels, down the left hand side, these are in ROWS. This is referred to as the ROWS area or sometimes the Row Labels area in the PivotTable. Across the top here, these are Column Labels that corresponds with what we see in the PivotTable Fields list in this lower portion, the COLUMNS area, and the numbers here are in the section called the VALUES area. Now, if you're working with the data, you're the analyst, you're looking at the numbers, considering some other layouts, maybe, it's certainly not going to bother you to see this, and even in some presentation venues, that's not so bad either, but I think most of us would probably rather see either nothing here or perhaps the actual field labels that we see, in other words, Salesperson and Product. Now, there's something else that you might have recognized. Not only do we have this PivotTable Fields list to the right, we've got a new Ribbon in the menu system called PivotTable Tools. There's an Analyze tab, with buttons related to PivotTables, as well as a Design tab, also related to PivotTables. Click outside the PivotTable, as I'm doing now, those disappear, and also that field list to the right. Click back within the PivotTable, field list reappears on the right, and we also have our two contextual tabs up above. If you don't want to see these terms right here, one option is, on the Analyze tab off to the right, we can simply turn off Field Headers. I think you could make the case for saying, "Well, they're not really needed here, "we know these are Products, "we know these are Salespersons, so maybe we need nothing." I think a better approach is bring them back, and go to the Design tab, and this is certainly not an obvious choice at least first time around, but if you go to Report Layout, you'll see, and this, of course, would not be obvious either, we are currently viewing the PivotTable in what is called Compact Form. The two other forms, Outline Form and Tabular Form, actually give us the field headings in place of those terms, Row Labels and Column Labels. Outline and Tabular are very similar. The major difference is that Tabular shows gridlines. There's another difference that we might see later. Tabular Form, there we go, with the gridlines, Outline, without, but more important, I think, to a lot of people, Salesperson and Product, and particularly if you were printing this, I think you'd like to see that on the worksheet, or making a presentation as well. Now, in this look, keep in mind, standard Excel features are pretty much available, too. We'll click in the upper left corner, selecting the entire worksheet, double click any column boundary to readjust the column width, that is important. We could also angle the text if we want to, the top row here, that's certainly not critical but we could do that as well, too. We've got good solid information here and as you'll see in upcoming movies, we have the ability to quickly manipulate this data and display it in different ways. It doesn't take long to create a PivotTable. In a quick recap here, going back to our source data on the Creating tab, we simply, first of all, made sure our data was contiguous, row after row, no empty rows, no empty columns. We clicked within the PivotTable, went to the Insert tab, clicked the PivotTable icon, and from there, simply clicked OK, and we ended up on a new sheet. Remember, initially, we didn't see anything on the left side, but then in the PivotTable Fields list to the right, we clicked the box for Salesperson, we clicked the box for No. of Items, and then dragged the word Product into the COLUMNS area, and there's the PivotTable. Fast, easy, and some pretty impressive analysis at our fingertips.