Course / PivotTables

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

  1. What is a typical order worth in each region? Double-click the Sum of Sales heading in I1 and set Summarize by to Average.

  2. Add a second value: tick Units. It goes into Values next to the average, as Sum of Units.

  3. How many orders did each region take? Drag Product into Values. A text field in Values is counted.

  4. Finally, the biggest single order in units: double-click the Sum of Units heading in J1 and set Summarize by to Max.

Remember

Previous: Your first PivotTable · Next: PivotTables: rows, columns and percentages