PivotTables: count, average and more values
Change Sum to Average, put several values side by side, and count orders with a text field. Intermediate · 5 min · PivotTable, Value Field Settings, Count, Average, Max
A value in a PivotTable doesn't have to be a sum. Each one can be summarised as a Sum, Count, Average, Max or Min, and you can show several values side by side.
The PivotTable at H1 already totals Sales by Region. You'll ask it more questions.
To change how a value is calculated, double-click its heading in the PivotTable, such as Sum of Sales. In Excel that opens Value Field Settings; here it opens the field's settings in the PivotTable Fields panel. Clicking the field under Values in the panel does the same.
Steps
What is a typical order worth in each region? Double-click the Sum of Sales heading in
I1and set Summarize by to Average.Add a second value: tick Units. It goes into Values next to the average, as Sum of Units.
How many orders did each region take? Drag Product into Values. A text field in Values is counted.
Finally, the biggest single order in units: double-click the Sum of Units heading in
J1and set Summarize by to Max.
Remember
- Every value can be a Sum, Count, Average, Max or Min (in Excel: Value Field Settings).
- Several values sit side by side, each with its own calculation.
- Text fields in Values are counted, which is the quickest way to count rows per group.
- Grand totals are worked out from all the rows, so an average total is the true overall average.
Previous: Your first PivotTable · Next: PivotTables: rows, columns and percentages