From the course: Excel 2013 Essential Training

Creating PivotTables - Microsoft Excel Tutorial

From the course: Excel 2013 Essential Training

Start my 1-month free trial

Creating PivotTables

“

One of Excel's most powerful and creative tools is a PivotTable. It's the ideal feature for making sense out of a large amount of data. In this worksheet called Creating, in the Workbook 13-PivotTables, we've got over 900 rows of transactional data here representing sales, usually multiple sales on a given sale. We've got our Salesperson, Product, our Customers are major retailers and they are located in four different regions in the United States and we'd like to be able to get a good quick summary read on what's happening here. The data currently isn't sorted, it doesn't need to be. Think of a PivotTable as a separate entity, we're about to create one that will quickly give us summary information about this list. If you look at this list for a little bit, one of the things you might want to see would be a list of our Salesperson's names down the left-hand side and across the top maybe the products that they've sold and then how many at the intersection or possibly the dollar amount. I've done this manually off to the right, it's in column J. I'll just make column I narrower and scroll over there a little bit. Here is the kind of list I'm thinking of: Customer names down the left-hand side, Products across the top and we can see how many Items have been sold 7997, almost 8000 items had been sold and we quickly see who the top Salesperson, we see which item is selling better than another one, this is good information. Take a look at some of the formulas required to get this though. This formula, not exactly easy, SUMIFS uses a function that's rather sophisticated and setting this up is going to take some time. Furthermore after doing this, could we easily change this display so that we showed for example the customers across the top, which customers are buying from which salespersons? And as we look at the different fields there, we might want to compare, for example, Region and Product, or Product and Salesperson, Customer and Product, a variety of different interrelationships, somewhat like we're seeing over here in column J. With the PivotTable, we can create a list like this in under 10 seconds without formulas. It's going to be that simple, and furthermore we won't have to worry about formulas like this one, we can easily change the PivotTable to include other fields across the top here. The PivotTable actually is a separate creation, typically placed on another worksheet, although we could place it on this very same worksheet. Although the feature is database-like in many respects, it's found on the INSERT tab in the Ribbon. A PivotTable not only gives us this quick ability to summarize information, it also allows us to sort the data, it allows us to filter it, it does consist of a number of database technics that you might already be familiar with. We begin the process by simply clicking within the data. Now you just can't take any collection of data and say, well I'll use a PivotTable and analyze what's going on. The data you're working with has to be list-like in nature and that means you want your titles in a single row. If you other information above it, that's fine but it's best to keep the row above it empty if you do have other information up there and make sure that within the data that you don't have any empty rows or empty columns and that does facilitate the creation of a PivotTable because we need only click within this data here. If you do have other data to the right as I do here, make sure you've got at least one empty column isolating that data from the data that you want to work with. So the active cells within here, let's create a PivotTable rather quickly. INSERT tab, left button, PivotTable. The Create PivotTable dialog box tells us the data that it thinks we want to use and it nearly always gets this right so a quick look here. This goes down to row 213 out to column H, looks good. The default location for PivotTable, new Worksheet. Let's go just with it. Recognize you could put it on the Existing Worksheet right here. We could do that if we wanted to. Let's just click OK. We're now on a new worksheet. Off to the left, we see a PivotTable placeholder and off to the right a PivotTable Fields list. We can move this over if we wish, you don't have to do this; you can make it a bit bigger as well. As we make it bigger here, recognize we are seeing the field names right here that we saw in the previous sheet and possibly you can move this down to see them all, sometimes you'll do that but that's not critical but that could be helpful at times too. Here are the fields that we're interested in. I'm going to click the box for Salesperson. Two things will change on the screen. Off to the left and alphabetized list of our salespeople and in the lower portion of the PivotTable Fields list, we see in the area called Rows, Salesperson. Let's now click the box for Items, let's say we want to track the number of items sold. That's a numeric field. If we check the box, it automatically goes into the Values area that may be just as important over in column B, we see the sum of items for each salesperson and there is that 7997 total that we saw on the previous worksheet. Now something you wouldn't expect at first, if we do want to show the product here, we're going to drag the word Product into the Columns area and there's our PivotTable, we can move this to the right. The same total that we saw before and if we were to compare the numbers with that manually created table, the same numbers in every single case, nowhere here do you see a formula, click here, click there, look in the Formula bar just the numbers are here, pretty fast. As you look at this you might say, what's this Row Labels, Column Labels? If it's only you working with the data, don't worry about those. The focus is on the numbers and what they mean, but this does reinforce the idea that this portion of the PivotTable has a name, it's the Row area or the Row Labels area, you could use either term. We also see this term off to the right, in the lower portion of the PivotTable Fields list, we see the term Rows here. Salesperson is in the Row Labels area or the Row area. Column Labels, that's the term we see across the top, we see that also reflected in the PivotTable Fields list to the right. Now if you are making a presentation of this data, you might not want to see these terms here, you might have noticed already that when the active cell is within the PivotTable anywhere here we've got a PivotTable tools Ribbon in our menu system with an ANALYZE tab and a DESIGN tab. Also whenever the active cell is within the PivotTable, we do see the PivotTable's Field list to the right. I'm about to click on cell E1, watch what changes. That field list to the right is gone and the menu that we had seen is gone as well. As I click back into the PivotTable, both of those features return. If we don't want to see these terms here, Column Labels and Row Labels we can go to the ANALYZE tab and simply not show Field Headers. In some cases that might be appropriate, surely those are products we sell; these are our salespersons so we don't necessarily need labels there. But I think a better choice will be, let's return those Field Headers and then let's change these cells so that they actually reflect the names of the fields. And the way we do that is to go to the DESIGN tab and choose Report Layout. Now you wouldn't instinctively know that we have been viewing Compact Form. The other two variations Outline and Tabular do show the field names. The difference in these two is that Tabular shows gridlines, let's just choose this one and now we see Salesperson instead of Row Labels and Product instead of Column Labels. We also see those two terms in our PivotTable Fields list to the right, down here. The more you work with PivotTables, the more you appreciate the PivotTable Fields list and the fact that the lower portion of it serves as kind of a map of the PivotTable itself. A couple of other quick changes you might want to make here, you might click in the upper left-hand corner, Double-Click a column boundary to readjust the column widths, now it's not critical but any other feature that you might be familiar with formatting, you can certainly use it too. So it certainly didn't take us very long to get here. We have no formulas in sight and we will as you'll see in an upcoming movie the ability to quickly change the layout of a PivotTable. It's very fast to create a PivotTable as you've seen and you will have the choice at any time to put the PivotTable on a separate sheet as we see it here or on the same sheet as the source data.

Contents