Separate Years in PivotTable Dates

 Separate Years in PivotTable Dates

In Excel 2016, Microsoft updated PivotTables to automatically group date fields. This can have some unexpected results. With the recent New Year, a friend of mine noticed that the PivotTable he created last year was grouping dates by Month, but it was combining Jan 2021 dates with Jan 2022 dates in the single Months field.

The solution is to force grouping the date field by Year and Month. Then include the Year field first, followed by the Month field. This is addressed in the Microsoft Support article at https://support.microsoft.com/en-us/office/group-or-ungroup-data-in-a-pivottable-c9d1ddd0-6580-47d1-82bc-c84a5a340725.

Now the Microsoft Support page doesn't specifically call out this issue or that changing the grouping as the solution. If you can find a better article from Microsoft Support, please add it to the Comments below.

I found the answer in one of those help websites that is 20% help and 80% online ads. These websites are income generating websites for their expert. These websites annoy me. They distract you with dozens of ads that are videos and other flashing images. I really don't want to promote them. Unfortunately the Microsoft Support page I found doesn't make it easy to figure out how to fix the issue in your workbook. So, with the understanding that there are more ads than information, if you're still not sure how to group the years of the data before grouping the Months, check out https://www.contextures.com/xlpivot07.html carefully watch the tutorial video.