Group Daily Dates by Month in a Pivot Table
December 26, 2022 - by Bill Jelen
![Group Daily Dates by Month in a Pivot Table Group Daily Dates by Month in a Pivot Table](/img/excel-tips/2022/12/group-daily-dates-by-month-in-a-pivot-table.jpg)
Problem: My data set has a date on which each item was shipped. When I produce a pivot table with the date field, it provides sales by day. My plant manager loves sales by day, but everyone else in the company would rather see sales by month.
![From the first date cell in the pivot table, choose Group Field from the Analyze tab in the ribbon.](/img/content/2022/12/LE10000867.jpg)
Strategy: You can group daily dates to show year, quarter, and month. To do so, you build a pivot table with dates in the Row area of the pivot table:
1. Select a cell that contains a date. Click the Group Field icon in the Analyze tab. Excel displays the Grouping dialog.
2. The Grouping dialog defaults to selecting months. If your data spans more than one year, it is crucial that you also select years. Select Months and Years. If you don’t choose Years, Excel will group January from one year and January from another year into a single value called January.
The Date field is now replaced with Months. There is a field called Years. Years and Months are shown in the pivot table, although the pivot table is not showing subtotals for each year.
![The Grouping dialog offers Seconds, Minutes, Hours, Days, Months, Quarters, and Years. Choose Months and Years.](/img/content/2022/12/LE10000868.jpg)
3. To add the subtotals for the years field, select a years field, then choose Field Settings as shown.
![The daily dates in the pivot table are replaced with 24 rows of months and years (the data set spans two full years)](/img/content/2022/12/LE10000869.jpg)
4. In the Field Setting dialog, change Subtotals from None to Automatic.
![Go to Field Settings for the Years field and set the Subtotals to Automatic.](/img/content/2022/12/LE10000870.jpg)
The result is a report with a subtotal for each year.
![The pivot table includes a total for each Year after the 12 months.](/img/content/2022/12/LE10000871.jpg)
This article is an excerpt from Power Excel With MrExcel
Title photo by Ralph Mayhew on Unsplash