Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you create a PivotTable, you're often interested in finding the sum of the values in the source data. If you'd rather find the average, minimum, maximum, or other summary value for the data in a row or column, you can change the summary operation that appears in the PivotTable. So taking a look at the PivotTable once again, you'll see that my grand total column here and row here, find the sum of all the data in the columns or in the rows, and then finds the sum of both the column and the row here, so I have one grand total.
If I want to change this operation, in other words change the summary that appears in the grand total columns and rows, then I can click any cell that's in the body of the PivotTable and then on the PivotTable contextual tab, all the way here on the left in the Field group, I can click Settings. When I click Settings, the PivotTable field of dialog appears, and you'll see that down here on the left, I can change the summary operation. So, for example, if I want to change the summary to average, I can click Average, and when I click OK, Excel changes my summary operation to Average.
Now you notice also that the summary operation in the interior of the PivotTable changed, and that's because in this PivotTable, I happen to have data from two years. So what you see is the summary of the data for two years. If I were to add the Year field to the Row Labels area, which I will do now, and I'll add it above Month, dropping it in, you will see that now we have values that are individual values. So for January of 2008, we have FirmA and FirmB, with a grand total, in this case an average of 98.5, which is correct for those values.
You can also go down to the bottom of the column, and for FirmA, you see that the average monthly revenue was 80.25. And I'll just show you one more time, and I'll change these summary operation to minimum, so we can see the lowest value. So I'll go back up to Settings, and again I have a cell in the body of the PivotTable selected. Click Settings, change to Minimum, and click OK. Now the summary field shows the minimum value, which in this case, for each row, or each column, we have 24.
That is the smallest value, 14 also the smallest value, and 14 is the minimum or smallest value for all of 2008. So it appears here in the summary for the year 2008. Changing the summary operation you use for a PivotTable field helps you gain important information about the values it contains. By comparing individual values to a column or row's average, for example, you can determine how individual value is compared to your organization's overall performance.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97983 Viewers
80 Video lessons · 141249 Viewers
59 Video lessons · 59616 Viewers
52 Video lessons · 72871 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.