MrExcel.com - Your One Stop for Excel Tips & Solutions

Group Daily Dates by Month in a Pivot Table

December 26, 2022 - by

Group Daily Dates by Month in a Pivot Table

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.
Figure 894. Excel reports daily dates.

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.
Figure 895. Select Months and Years.

  • 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)
Figure 896. 500 rows of daily dates are now 24 rows of months.

  • 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.
Figure 897. Change the Years field to use Automatic Subtotals.

The result is a report with a subtotal for each year.

The pivot table includes a total for each Year after the 12 months.
Figure 898. Totals by year.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ralph Mayhew on Unsplash