Pivot tables are an easy way to quickly count values in a data set. In the example shown, a pivot table is used to count the names associated with each color.

Fields

The pivot table shown is based on two fields: Name and Color. The Color field is configured as a row field, and the name field is a value field, as seen below:

Pivot table basic count field configuration - 1

The Name field is configured to summarize by count:

Pivot table basic count value field settings - 2

You are free to rename “Count of Name” as you like.

Steps

  1. Create a pivot table
  2. Add a category field to the rows area (optional)
  3. Add field to count to Values area
  4. Change value field settings to show count if needed

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.
  3. Without a Row field, the count will be a global count of all data records.

Pivot tables make it easy to quickly sum values in various ways. In the example shown, a pivot table is used to sum amounts by color.

Fields

The pivot table shown is based on two fields: Color and Amount . The Color field is configured as a row field, and the Amount field is a value field, as seen below:

Pivot table basic sum field list - 3

The Amount field is configured to Sum:

Value settings for Amount field - 4

You are free to rename “Sum of Name” as you like.

Steps

  1. Create a pivot table
  2. Add a category field the rows area (optional)
  3. Add field to count to Values area
  4. Change value field settings to show sum if needed

Notes

  1. When numeric field is added as a Value field, Excel will display a sum automatically.
  2. Without a Row field, the sum will be the total of all Amounts.