Learn How to Manipulate PivotTable Data in Excel 2013

show more Manipulating PivotTable data provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training show less
please wait ...

Manipulating PivotTable data

One of the best features of a Pivot Table is the ease with which you can manipulate the fields. We're looking at a worksheet called Manipulating, it's in the Workbook 13-PivotTables and we're looking at a Pivot Table that was created earlier and we're thinking of changing the layout of the fields as we see them here. The Pivot Table fields list is off to the right. Sometimes this is anchored, you can click and drag its title and place it above the data if you wish or closer to the data and also make it larger this way. Again, as we look at the Pivot Table field list Product, Items, Salesperson, that's the layout we see to the left.

Imagine if you're making a presentation and the discussion about what we're seeing here has pretty much run it's course and the question that has come up, could we see this breakout by customer, possibly customer and region or customer and salesperson. So sometimes we want to make a change. What if I like to see Product down the left-hand side? I'm not interested in Salesperson for the moment, so I'll take Salesperson out of the Row Labels area. You can do it one of two ways. You can click and drag and remove it this way, another way is simply to uncheck the box above.

For the moment we've got a pretty small Pivot Table, not very valuable. But let's place Product in the Row Labels area and then let's see who are the buyers of this product, perhaps we'll bring in Customer and put that in the Column Labels area and now we see this layout. We have the same total that we saw before, 7997 but a different layout. We don't have to worry about formulas, we still don't see any formulas here but we can easily change the appearance of this and another thought might have occurred to you, what if a few years ago you created a list like this, perhaps it was done manually and you like to recreate that list and in that list the customer's names were down the left-hand side, in that portion that we refer to as the Row Labels area and the Product was across the top, in that area we refer to here as the Column Labels area.

So if you were to think of the data this way and you'd like to flip the data, so to speak, or transpose it, the term pivot comes to mind. We're going to pivot the data from the upper left-hand corner and actually that's not a single action, it's two actions. We're simply going to reverse the order of Customer and Product and that means dragging these two fields to their opposite location and in no particular order. So I'm going to drag Customer from the Column Labels area into the Row Labels area and we see an interim view there that occasionally might be okay.

Let's drag Product to the Column Labels area and there is that layout. And as we do this at different times we might want to click in the upper left-hand corner and Double-Click our boundary to adjust the column widths. Based on what we've seen so far, you might imagine that a Pivot Table is mostly about comparing two fields. Well it often is, but certainly it isn't limited to that. If we want to show this breakout here also by Salesperson, we could bring that field back into list. Because that's a text field, if we'd click the box for Salesperson, it will automatically appear in the Row Labels area.

If we want to go to Column Labels area we have to drag it there. So I'm just going to check the box for Salesperson and we see the list here. Now that's not very compact, that doesn't mean it's bad. But if we were to print this list, we can see that it's certainly lengthier than one we've been seeing but 70 rows is not outrageous, that's a reasonable amount of information and a lot of detail too. If we change the order of Customer and Salesperson, not exactly an instinctive idea, but let's do it anyway, watch what happens to our list. Currently as we view the Pivot Table, Customer is to the left of Salesperson.

As we look at the Pivot Table field list, we see Customer above Salesperson, so let's drag Customer downward and now we see the display this way. A different way of viewing the data and it's up to you to decide which you like best; you can change your mind at different times too. You might also be seeing as we do this, for example subtotals. You may or may not want those. If you don't, go to the DESIGN tab, Subtotals, Do Not Show Subtotals, we see the list this way. And once again we might want to switch back and forth the order of Salesperson and Customer just to see how it's looking.

And at different times you want to see a list a certain way, you can see it another way. All the while we're manipulating a Pivot Table; there is no possibility in any way of altering the original data. Think of the Pivot Table as a separate entity and we can change the layout any number of different ways. There might even be times when we say, you know I don't want to see this salesperson here but I want it nearby so I can get to it quickly. We can drag Salesperson into what's called a Filters area, right there. Recognize that in the Pivot Table now, we have an upper portion here, this is the Filters area.

The advantage here might be we only want to see data about one person or maybe two of our salespersons, click the drop arrow there and maybe we just want to see how well Emily has been doing, there is Emily only, her sales, 632 items sold altogether. And you've got other options up here as well, there is a choice for selecting multiple items, let's say we also want to see in our list here John Lucas and now we are seeing jus the data for those two people. So the Filters area has a role to play as well.

If we no longer need to see this differentiation, we could just possibly remove Salesperson from the list completely or possibly bring it back into the Row Labels area. Now there's another aspect of Pivot Tables that does surprise people. It's almost as if we have created a chart here in the sense that we've created something different, it's a different view of the data, it's certainly based on data in a different worksheet in this example, but what happens if the source data changes? So as we look at this list here, remember the total is 7997, let's suppose our list here is showing the Salesperson and the Product.

So I'm going to remove Customer, this time dragging it off, click the box for Salesperson, we see our salespersons here and I don't know if you remember but a few minutes ago when I actually removed it from here, we were using a filter and now that filter is still there. So if we look at Salesperson over on left-hand side and click the filter arrow there, there we might want to now Select All of the names there, click OK. In this list our total 7997, our top seller here is Harlan Vaughn 1068.

I want to show you what happens here and this might be a surprise if you haven't worked with Pivot Tables very much. I'm going back to the source data. It's on the sheet called Creating. I'm going to make a change to one of the records. We've got an update here and actually 12 items were sold here. Again, remember our original list is 7997. If this is going to be 12 that list is going to go to 8000. So I'm going to change this to 12, that will have the impact in the cell to the right which is calculating a total, you would expect that to change and surely our Pivot Table will have changed, right? If we go to Manipulating, it still says 1068; we still have the same total as before.

So what's happening here? In Pivot Tables, even though they are based on source data on another worksheet possibly or maybe on this worksheet, Pivot Tables don't change when the source data changes. You have to make that happen. And so with the active cell within the Pivot Table, go to the ANALYZE tab in the Ribbon and choose Refresh. Also there is a keystroke shortcut, Alt+F5. And what has happened, it's now 8000 and this number here which had been 1068 is now 1071.

So refreshing your data is something you need to do as you work with Pivot Table data. And there is another aspect to this as well as you work with Pivot Tables. Suppose you're having a meeting here and you're looking at the numbers and for whatever reason, one number jumps out at this person to that person, somebody wants to see the detail behind one of these. Who are the 19 customers here who purchased the bamboo coffee table or Icelita Kelly was the Salesperson? Double-Click, what's happened here, we're in a brand new worksheet and we're seeing here a list, these items here do total, 19 there they are.

This represents the source of that number, in other words what we're seeing here and let's adjust the column to make it a little bit more readable. This is data gathered from the source list and placed on a new worksheet in table format and so this represents the 19 that I Double-Clicked on. Here are the 19 items that were sold. And so as we go back to the Pivot Table right here, when I Double-Clicked 19, suddenly it's as if we go back to the original data we find these 19 items and we see them on a new worksheet, that's called drill down and we can Double-Click on any of the numbers in this list here and get the same effect if we're interested in that.

Now, if we make changes to our source data and then update this Pivot Table, this sheet isn't getting updated and so it could be incorrect. So when you do create these lists, keep in mind that you might want to get rid of them pretty quickly. But if you're not changing the data that much, they're going to be accurate until you make changes. If you no longer need the sheet, simply Right-Click and delete it. But it is a great feature called drill down and sometimes you'll want to do that. So we can see as we work with Pivot Tables we change our minds sometimes about the appearance of it, what it is we're looking at, we have the ability to move these fields back and forth.

Sometimes we're just looking for a better display; sometimes we're really looking to analyze the numbers in greater depth. The capability to manipulate and change the appearance of a Pivot Table is one of its greatest strengths.

Manipulating PivotTable data
Video duration: 9m 47s 6h 32m Appropriate for all


Manipulating PivotTable data provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training

Business Education + Elearning
please wait ...