Join Dennis Taylor for an in-depth discussion in this video Creating PivotTables, part of Excel Essential Training (Office 365).
- [Instructor] One of Excel's most powerful analytical tools is what we call a pivot table. It's a great tool for creating quick summary totals based on a list of data. And on this worksheet here called pivot table data in the workbook 10, data management features, we've got a list of about 900 rows. It's not sorted, as it often might be, doesn't need to be to create a pivot table. A list like this should have no empty rows or columns within it. What we'd like to be able to do is to come up with some good, quick summary information here. So off to the right, I've done this manually ahead of time with kind of a list you could imagine wanting to get.
It's a list of all the salespersons here. They can sell products to any regions, so we see how that's broken out by region. And how do we get to something like this? Here's a formula here in cell J4, I'll double click it. Not the most difficult of formulas, but on the other hand, it's gonna take you some time to come up with answers like this and verify them, and we wouldn't have a lot of flexibility here if we had to quickly turn this around and put in another field up here, for example, product or customer instead of this. We can create a list like this with a pivot table in about five or 10 seconds without formulas.
That's what we're going to do. Check out the list ahead of time, you make sure there's no empty rows or columns, and you only click within the lists, and although it is a data management-like tool in one respect, it's not found on the data tab. It's on the insert tab. So click pivot table, Excel nearly always gets it right. It figures out the extent of the data. We could put this on the same worksheet, but more likely, go with the default setting here, new worksheet. Many times, you'll just glance, click okay. We're on a new sheet. Off to the right is the all-important pivot table fields list.
Shows the same column headings, as we saw before. We can make this wider and narrower. A couple of clicks and a drag and we will have a pivot table. I'm gonna click the box for salesperson at the left hand side of the screen. That's a unique and alphabetized list of our salespersons. I'll zoom in a bit using the slider bar, bottom right of our screen. I'm gonna click the box for items. Watch those numbers over there, and I'll drag the word region into the columns area. That's it. Now you might have noticed before and I'll point out our best salesperson, Willy Loman, right there, grand total, 8111.
Not a formula in sight. Let's jump back to the source data, one sheet to the right. They're those same numbers out there, there's Willy Loman. 1073, grand total, 8111. We got here pretty fast. Not quite right, maybe with these, nothing wrong with those, but they'll do something else. When the active cell is within the pivot table, we've got a new ribbon, pivot table tools with a contextual analyze tab and a design tab. Now you wouldn't know instinctively what to do here, but on the design tab, report layout compact is the default.
If we choose outline or tabular, we'll see the actual field names there. I think most people would probably want that, so salesperson, region, looking good. And if we were making a presentation, you could imagine somebody in the audience might say something like, "Okay, we see the breakout by region. "How about by product?" And what do we do? In the pivot table field list to the right, we'll uncheck the box for region and drag the word product into this area called columns. Now you quickly start beginning to see how the lower portion of this pivot table fields list is like a mapping of the pivot table.
We've got products here, cross these column headings. Salespersons are in rows, so we see the term rows, columns out here. And no time are we seeing any formulas. And not only can our comparison be just between two fields here, product and salesperson. We could bring region back here. I'll put it in the rows area. Watch the screen on the left hand side, and I'll zoom back again with that slider bar a bit. Let's see what's happening. Now that's maybe contrary to what a lot of people envision as a pivot table.
Usually, it's compact, give me the big picture sort of thing, but in some cases, this would be a appropriate. We can make some other changes. Here's a design tab. Off to the left are subtotals. We might not want to show those, or maybe we'll just move these fields around in different ways, making all kinds of different comparisons. Maybe if we compare or change the order of this, drag salesperson below region, we see a different layout that way. So there's just so many different ways to view the data. We never worry as we do this, too, about disrupting or threatening our source data. And one little oddity that you get used to, at least initially, is this idea.
If we do make changes to the source data, the pivot table does not adjust automatically. You go to the analyze tab and refresh the data or use that keystroke shortcut, alt F five, so that's something to keep an eye on. But it's an amazingly powerful tool. It gets us here quickly, we create these summary lists, we manipulate the data in a variety of ways. It's just indispensable for any kind of situation where you're trying to analyze data from a list.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting your data
- Adjusting rows and columns
- Finding and replacing data
- Inserting and deleting sheets
- Sorting and filtering data
- Creating charts and PivotTables
- Printing and sharing worksheets
- Protecting worksheets and workbooks
Skill Level Beginner
Q: This course was updated on 1/7/2019. What changed?
A: A new video was added that covers working with Excel Ideas.