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 added as a Value field:

Pivot table two-way average Field list - 1

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

Pivot table two-way average value field settings - 2

The custom name can be anything except an existing source field name (i.e. not “Age”, “Gender”, or “Rating”).

Steps

  1. Define data as an Excel Table (optional)
  2. Create a pivot table based on a table or data
  3. Add the Age field to the Rows area
  4. Add the Gender field to the Columns area
  5. Add the 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.

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

Fields

The pivot table shown is based on three fields: Color, Size, and Qty. The Color field is configured as a Rows field, and the Size field is configured as a Columns field. The Color field is also configured as a Value field.

Pivot table two-way count Field list - 3

The Color field is configured to summarize by count in the Values area:

Pivot table two-way count value field settings - 4

Because the colors are text values, the pivot table automatically performs a count instead of a sum. You are free to rename “Count of Color” as you like.

Steps

  1. Define data as an Excel Table (optional)
  2. Create a pivot table based on table (or data)
  3. Add Color field to the Rows area
  4. Add Size field to the Columns area
  5. Add Colors field to the Values area

Notes

  1. Any non-blank field in the data can be used in the Values area to get a count.
  2. When a text field is added as a Value field, Excel will display a count automatically.