Exceljet

Quick, clean, and to the point

Pivot table count by year

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

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

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

Pivot table count by year date group settings

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.

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.