Join Curt Frye for an in-depth discussion in this video Changing the data field summary operation, part of Excel for Mac 2016: Pivot Tables in Depth.
- [Voiceover] Most data lists that you summarize using pivot tables will have some sort of financial or personnel data inside. Those could be sales or hours worked, which means that you'll usually want to find the sum of the values in the list. You are not limited to adding values together, though. You can choose from several summary calculations, and also have the ability to change the type of value shown in the data area. For example, you can express individual values as a percentage of the column or row total, or compare values to those of the previous year.
In this movie, I will show you how to change the summary operation for the values field. My sample file is the Summary workbook, which you can find in the chapter two folder of your exercise files collection. This pivot table contains yearly data for two different room types, Cambridge and Piccadilly, in the years 2014 and 2015. Let's say that, instead of displaying the sum of the values for the monthly data that I've collected, let's say that I want to show the average.
To do that, I can click any cell within the data area, in other words, within the values area of the pivot table. Clicking a label cell wouldn't work in this case. So click any cell in the values area, and then, on the pivot table Analyze contextual tab, click the Field settings button. Doing so displays the PivotTable Field, and you can see that the field name is Sum of Revenue. I'm on the Summarize By tab, so I can change to another summary operation.
As I said before, let's say I want to change to average. So I'll click Average and then click OK. Doing so displays the average monthly values for my two room types and my two years. It's nice that I can change the summary operation, but let's say that I want to find the contribution of each year and room type to the whole. To do that, again with a cell in the values area, within the body of the pivot table selected, I can to go the Analyze tab and click Field Settings.
In this case, I'll switch back to Sum and click OK. And I go back again and click Field Settings. Now I'm going to click the Show Data As tab. That displays other tools that I can use to make comparisons. The type of calculation that I'm doing right now is called Normal. If I click the list control, I can see the other items that I can use. So I can look at a Difference From, a particular field, % Of, % Difference From, and so on. In this case, let's say that I want to find the percentage of each row.
If you look back at the pivot table itself, you'll see that Cambridge and Piccadilly are column headers. So if I want to see the percentage of their contributions for 2014, I would look in cells B5 and C5, so I need to look at the row. I'll click % of row. I don't need a base field. I'll go ahead and click OK, and there I have it. I see that, in 2014, Cambridge rooms provided 47.79% of income, Piccadilly, 52.21, and you can see the numbers for 2015 right below them.
You should take some time to experiment with the summary operations and settings available for use in the values area. You'll probably find one or more operations other than addition that provide meaningful information about your business.
- Formatting data for use in a PivotTable
- Creating a new PivotTable
- Creating a Recommended PivotTable
- Connecting to external data sources
- Summarizing totals and other data fields
- Creating calculated fields
- Summarizing field data
- Sorting and filtering PivotTables
- Working with Excel slicers
- Formatting PivotTables styles and layouts
- Applying conditional formats