You don’t have to use workarounds or make changes to the registry. If you’re using Excel 2019 or Excel for Office 365, there’s good news. That removes the extra columns, and just leaves the original date field. or, right-click on one of the date fields, and click Ungroup.press Ctrl+Z, immediately after adding the date field.To get rid of the date grouping in a pivot table, Extra columns were created, to show the Years and Quarters, as well as the Order Date.
Here’s a pivot table in which I added the Order Date field to the Rows area. Starting in Excel 2016, dates are automatically grouped in a pivot table. Click the Workbook Appearance command, then click Toggle Filter Grouping. Or, if you have my Excel Tools add-in, that macro is built in. If ungrouping dates is something that you do frequently, you can use a macro to turn the grouping option on or off. Remove the check mark from Group Dates in the AutoFilter menu.
Here is a link to the page How to Change the Setting This video shows the steps, and the written instruction are below the video. This is a workbook level setting, so it will affect all the AutoFilters in the active workbook. If you want to see the individual dates, you can change one of the Excel options. Here’s how you can undo or prevent grouped dates in Excel AutoFilters and Pivot Tables.īy default, when you turn on an AutoFilter, the dates are grouped by year and month in the drop down list. This happens in AutoFilters, and in pivot tables too (in newer versions). If you have strict parents, that might be the only kind of dates they allow! But, in your Excel worksheets, it can be annoying when dates are automatically grouped.