Group Date and Ungroup Month in Excel Pivot Table

This Excel tutorial explains how to group dates and ungroup month in Excel Pivot Table to prevent auto grouping of Pivot Table in Excel 2016.

Group Date and Ungroup Month in Excel Pivot Table

Since Excel 2016,  when you drag a date field to a Pivot Table Pivot Table Row, the dates are  automatically grouped by months instead of showing each date. This is extremely annoying because  it is tricky to ungroup the months if it is the first time you encounter this issue.

In this post I am going to explain how to ungroup month in Excel Pivot Table and how to group dates by year or month.

Note that there is an Excel Option called Group dates in the AutoFilter Menu in Excel option, it does not work for Pivot Table Row Grouping. This option is for normal Auto Filter, not Pivot Table, so don’t get confused.

Ungroup Month in Excel Pivot Table

Suppose we have a data source which are dates from 2019 to 2021.

 

Insert a Pivot Table, and then drag the date field to Rows,the dates are automatically grouped by Year, Quarter and Month by default.

In order to display the original dates without grouping, right click on the data, select Group.

In the dates Grouping options, you can redefine how you want to group the dates. To display the original  dates on each row, highlight Days then OK.

Now the months are ungrouped and are displayed on each row.

Alternatively, you can simply right click on the date, then select Ungroup. this will ungroup every grouping.

Outbound References

https://support.office.com/en-gb/article/group-or-ungroup-data-in-a-pivottable-c9d1ddd0-6580-47d1-82bc-c84a5a340725

Wyman W
Manager@IBM
Wyman is human resources professional specialized in implementation of HR information system.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *