Excel Tutorial - Sorting data with pivot tables

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. We've got a list of about 900 rows. It's not sorted as it often might be. It 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 lists you could imagine wanting to get. It's a list of all the salespersons here. They can sell products to any region 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 to form is, but on the other hand it's going to 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 ten seconds without formulas. That's what we're going to do. Check out the list ahead of time. Make sure there are no empty rows or columns. You need only click within the list. 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. Let's 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 let's go with the default setting here. New worksheet. Many times you'll just glance, click OK. Run a new sheet. Off to the right is the all-important pivot table fields list. Shows those 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 going to click the 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 going to 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, Willie Loman, right there. Grand total 8111. Not a formula in sight. Let's jump back to the source data. One sheet to the right. There are those same numbers out there. There's Willie Loman, 1073, grand total 8111. We got here pretty fast. Not quite right maybe with these. Nothing wrong with those, but notice 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 as default. If we choose outliner 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 across 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 down the left-hand side. And I'll zoom back again with that slider bar a bit and 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 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 are 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. We go to the Analyze tab and refresh the data or use that keystroke shortcut Alt F5. So that's 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.