Watching:

Summarizing more than one data field


show more Summarizing more than one data field provides you with in-depth training on Business. Taught by Curt Frye as part of the Excel 2010: Pivot Tables in Depth show less
please wait ...

Summarizing more than one data field

When most users visualize a PivotTable, they see it with a single data set in the data area. While that's certainly the most common data area configuration, you can summarize more than one data field at a time. So let's say for example that I have a monthly revenue worksheet for two firms and I already have the Revenue field in the data area. So if you look in the PivotTable Field List, you can see the Revenue field there and then you can also see the same data in the body of the PivotTable. Now let's say that I want to create a PivotTable that displays the revenue data for each month and I also want to show the same data but as a comparison.

Say for example, if I wanted to compare the values between FirmA and FirmB as a percentage. To do that I can add a second copy of the Revenue field to my data area, and to do that click any cell on the PivotTable and then on the PivotTable Field List drag the Revenue field header down to the Values area. Again, when I drag it down, even though the Revenue field is already there, if I drag the Revenue field header down to the Values area and release, then Excel creates a second copy of the Revenue field in that area, and you'll notice that the PivotTable changes.

I have Sum of Revenue, and then I have Sum of Revenue2. And then also in the Column Labels area in the PivotTable Field List task pane, you'll see that I have a Sigma values header. What that means is that for the column labels to create room for that second data set, they had to create a second values column. Having two copies of the same data summarized in the same way doesn't really help us, so what we need to do is change how one copy of that data is summarized. To do that you can right-click any cell in either of the two data areas.

So in this case I have Sum of Revenue2. I'll right-click a cell in one of those columns, point to Show Values As, and then I'll do percentage of, and then we'll make it a percentage of the previous month. So right now our Base field is Month. That's correct, and then I'll change the Base Item to (previous) and then click OK. So the value in January is 100% because we are comparing it to itself, but then the value in each successive month is compared as a percentage to the value from the previous month.

So for example, 69 is 102.99% of 67, 128 is 185.51% of 69, and so on. Now you don't have to have two copies of the same field in your data area. So for example, if I were to remove the second Sum of Revenue field, dragging it back to the Field area, I can also add another numerical value such as, for example, the number of sales and that data is in the Sales column. So I'll drag it down to the Values area.

And now I have the revenue for each month, and then I also have the number of sales. So for example, I could now determine that I had $67,000 of revenue based on this number of sales. That information gives me a lot more insight into my sales performance and also it allows me to perform other calculations that I'll show you how to do in a movie later on in this chapter.

Summarizing more than one data field
Video duration: 3m 16s 3h 43m Intermediate

Viewers:

Summarizing more than one data field provides you with in-depth training on Business. Taught by Curt Frye as part of the Excel 2010: Pivot Tables in Depth

Subject:
Business
Software:
Excel
Author:
please wait ...