Excel 2016 Sometimes Auto-Groups Daily Dates to Month

December 23, 2022 - by Bill Jelen

Problem: I just upgraded to Excel 2016. When I add dates to a pivot table, it sometimes automatically groups to months or months quarters and years. Other times, it does not.

Strategy: Most people could not figure out how to manually group daily dates by month as discussed in the next topic. New logic in Excel 2016 will automatically group daily dates if they span a long enough period. This feature was hated by many and turned off in Office 365.

Here are the rules:

If all of the dates are in one month, no grouping will occur.
If the dates start in one month and end in another month, the report will group by month and day. Only Month will initially appear.
If the dates start in one year and end in another year, the report will group by years, quarters, and months. But individual days will not be available.

The report will start with dates grouped to the highest level, as shown here:

The first column of the pivot table offers years 2018 and 2019. There is a plus icon to the left of each year. — Figure 892. Drag daily dates to the pivot table, and you might get this view.

Click on the cell for year 2018 and click the Expand Field icon on the Analyze tab. The years will expand and show quarters.

Expand the 2018 cell and new rows with Qtr3 and Qtr4 appear. There is a plus icon next to each quarter. — Figure 893. Expand years to quarters. Click on Qtr3 and expand again to see months.

If you don’t like the Auto-grouping, you can choose any date field and select Group Field to change the settings. Or, immediately after dragging Date to the pivot table, press Ctrl+Z to undo the grouping. Or - to turn it off permanently, there is a registry setting described at http://mrx.cl/stopautogroup. Office 365 subscribers: File, Options, Data. Choose Disable Automatic Grouping of Date/Time columns.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Spiske on Unsplash