Join Curt Frye for an in-depth discussion in this video Summarizing more than one data field, part of Excel for Mac 2011: Pivot Tables in Depth.
- [Voiceover] When most users visualize a pivot table, they see it with a single data set in the values area. While that's certainly the most common data area configuration, you can summarize more than one data field at a time. In this movie, I will show you how to add and configure additional value sets in the data area. My sample file is the more than one workbook, and you can find it in the chapter two folder of your exercise files collection. This workbook has a pivot table that contains a single revenue field in the values area, and what I want you to do is to add a second field, one that displays the value of sales, or revenue, from a particular quarter, for a room type, as a percentage of the row.
To do that, I'm going to add a second copy of the revenue field to the values area, so I'll go over to the pivot table builder, and drag revenue to the values area below the existing sum of revenue field, and you can see that Xcel adds a second copy of that field. Now, if I want to change the summary operation for my second data field in the values area, I'll click any cell in that field. In this case, I'll click cell E7, which is under the sum of revenue two-header, go to the pivot table contextual tab, and then over on the left edge of the tab, I'll click settings, and I'll just do a quick verify.
Field name: sum of revenue 2. Okay, good, that's what I'm looking for. In this case, like I said, I want to display the value as a percentage of the row total, so I'll click the options button, in the pivot table field dialogue box, click the show data as controls button, and click percentage of row, which is here, and then I'll click okay, and when I do, drag the pivot table builder out of the way. You can see that I have both the sum of revenue and sum of revenue 2 shows me the percentage that each of those values contributes to the rows, total, so in other words, I can see the only quarter in 2014 where the Cambridge Room returned more revenue than the Piccadilly Rooms was in quarter number two, and I can see that the percentage share was 51.54%, and, in fact, that quarter was the only one where the percentage share was above 50 percent, or another common way to use multiple data fields, is to have a column in the data source with the number of sales, and to display it next to the revenue each month.
That's a good way to determine whether a particular salesperson just got lucky, and got one huge sale, and didn't do much else, or whether your sales representative managed to accumulate good results through multiple sales of varying value. One thing to note is that pivot tables can get a bit crowded when you display more than one data field in the values area, but if you use the space wisely, you can gain a lot of insights by viewing two or more data sets simultaneously.
- Creating a PivotTable
- Refreshing PivotTable data
- Managing totals
- Creating calculated fields
- Grouping PivotTable fields
- Sorting and filtering PivotTable data
- Printing PivotTables