How to Prevent Grouped Dates in Excel
As a teenager, group dates can be fun. 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. This happens in AutoFilters, and in pivot tables too (in newer versions). Here's how you can undo or prevent grouped dates in Excel AutoFilters and Pivot Tables.
AutoFilter Grouped Dates
By default, when you turn on an AutoFilter, the dates are grouped by year and month in the drop down list. This screen shot shows an example.
Prevent Grouped Dates in AutoFilter
If you want to see the individual dates, you can change one of the Excel options. This is a workbook level setting, so it will affect all the AutoFilters in the active workbook.
This video shows the steps, and the written instruction are below the video.
How to Change the Setting
Here are the steps for changing the Group Dates option:
- On the Ribbon, click the File tab, then click Options
- Click the Advanced category
- Scroll down to the Display Options for This Workbook section
- Remove the check mark from Group Dates in the AutoFilter menu.
- Click OK to apply the setting change.
Use a Macro
If ungrouping dates is something that you do frequently, you can use a macro to turn the grouping option on or off.
Or, if you have my Excel Tools add-in, that macro is built in. Click the Workbook Appearance command, then click Toggle Filter Grouping.
Grouped Dates in Pivot Table
In Excel 2016, dates are automatically grouped in a pivot table. It's not a bug, it's an enhancement!
Here's a pivot table in which I added the Order Date field to the Rows area. Extra columns were created, to show the Years and Quarters, as well as the Order Date.
Remove the Pivot Table Date Grouping
To get rid of the date grouping in a pivot table,
- press Ctrl+Z, immediately after adding the date field.
- or, right-click on one of the date fields, and click Ungroup.
That removes the extra columns, and just leaves the original date field.
Avoid the Date Grouping
When I dropped a date field into the Filters area, I noticed that the individual dates were listed, instead of grouped dates.
Then, if I moved that date field into the Rows area, Excel didn't automatically group the dates – they still showed as individual dates.
So, you could use that 2-step process to add a date to the pivot table, to avoid the automatic date grouping.
Prevent Pivot Table Date Grouping
Unfortunately, there's isn't an Excel option that will prevent grouped dates in pivot tables. If you really want to prevent pivot table date grouping, you can make a change in the Windows Registry.