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 month for data that covers a 6-month period. The count displayed represents the number of records per month for each color.
The source data contains three fields: Date, Sales, and Color. Only two fields are used to create the pivot table: Date and Color.
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":
The Date field has been added as a Column field and grouped by month:
Helper column alternative
- Create a pivot table
- Add Color field to Rows area
- Add Color field Values area, rename to "Count"
- Add Date field to Columns area, group by Month
- Change value field settings to show count if needed
- Any non-blank field in the data can be used in the Values area to get a count.
- When a text field is added as a Value field, Excel will display a count automatically.
- Without a Row field, the count represents all data records.