Pivot table two-way average

Pivot tables are an easy way to quickly average unique values in a data set, and can easily be adapted to perform a two-way average. In the example shown above, a pivot table is used to average Ratings for unique combinations of Age and Gender, based on data in the range B5:D16, defined as an Excel Table.

Fields

The pivot table shown is based on three fields: Age, Gender, and Rating. The Age field is added as a Row field, and the Gender field is added as a Column field. The Rating field is is added as a Value field:

The Rating field in the Values area is renamed to "Avg Rating" and configured to Average:

The custom name can be anything except an existing source field names (i.e. "Age", "Gender","Rating").

Steps

1. Define data as an Excel Table (optional)
2. Create a pivot table based on table (or data)
3. Add Age field to the Rows area
4. Add Gender field to the Columns area
5. Add Rating field to the Values area
6. Change Rating calculation to Average
7. Rename Rating field (optional)

Notes

1. When a numeric field is added as a Value field, Excel the field is automatically summed.