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.
