Pivot tables have a built-in feature to group dates by year, month, and quarter. In the example shown, a pivot table is used to count colors per year. This is the number of records that occur for each color in a given year.

Fields

The source data contains three fields: Date, Sales, and Color. Only two fields are used to create the pivot table: Date and Color.

Pivot table count by year field list - 1

The Color field has been added as a Row field to group data by color. The Color field has also been added as a Value field, and renamed “Count”:

Pivot table count by year count settings - 2

The Date field has been added as a Column field and grouped by year:

Pivot table count by year date group settings - 3

Helper column alternative

As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the year . Then add the Year field to the pivot table directly.

Steps

  1. Create a pivot table
  2. Add Color field to Rows area
  3. Add Color field Values area, rename to “Count”
  4. Add Date field to Columns area, group by Year
  5. 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 represents all data records.

To display data in categories with a count and percentage breakdown, you can use a pivot table. In the example shown, the field “Last” has been added as a value field twice – once to show count, once to show percentage. The pivot table shows the count of employees in each department along with a percentage breakdown.

Fields

The pivot table shown is based on two fields: Department and Last . The Department field is configured as a Row field, and the Last field is a Value field, added twice:

Pivot table count with percentage field configuration - 4

The Last field has been added twice as a value field. The first instance has been renamed “Count”, and set summarize by count:

Pivot table count with percentage count settings - 5

The second instance has been renamed to “%”. The summarize value setting is also Count, Show Values As is set to percentage of grand total:

Pivot table count with percentage - percentage settings - 6 Pivot table count with percentage - percent of grand total - 7

Steps

  1. Create a pivot table
  2. Add Department as a Row field
  3. Add Last as a Value field Rename to “Count” Summarize by Count
  4. Add Last as a Value field Rename to “%” Summarize by Count Display Percent of Grand Total Change number formatting to percentage