By default, dates are grouped in the drop down list for an Excel AutoFilter, and in a Pivot Table filter drop down. See how to turn this automatic date grouping feature off, to show the full list of dates.
Automatic Date Grouping
If you have date drop down lists in your Excel worksheets, it can be annoying when those dates are automatically grouped.
This date grouping happens in:
In the sections below, see how you can undo those date groupings. You'll also see how to prevent grouped dates from happening, in Pivot Tables and Excel AutoFilters.
--Go to the section on Pivot Table Date Grouping
-- Go to the details for AutoFilter Date Grouping
Pivot Table Automatic Date Grouping
Pivot Tables dates weren’t grouped in earlier versions of Excel, but in Excel 2016, and later versions, dates are automatically grouped, when you add a date field to a pivot table. You can undo the grouping in Excel 2016, and turn that feature off in later versions.
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.
In the sections below, see how to remove the date grouping, and how you can prevent Excel from automatically grouping the dates in a pivot table.
Benefit of Pivot Table Date Grouping
Date grouping in pivot tables can be a helpful feature, and this archived blog post from the Excel team explains why this feature was added.
Normal Pivot Table Benefits
The date grouping feature can help in normal pivot tables too. For example, with a large dataset, Excel shows an error message if I try to put the date field into the Columns area.
However, Excel allows me to put the Date field into the Rows area, and it automatically groups the dates into Years and Quarters. That saves me the step of having to group the dates manually.
The new fields – Years and Quarters are also automatically added to the PivotTable Fields list.
With these grouped fields, I can move Years into the Columns area, and Excel won’t show an error message about the item limit.
Undo Pivot Table Date Grouping
So, the date grouping feature in pivot tables can be a real time saver, if you usually group the dates manually.
But, if you don’t like the grouping feature, there are steps you can take to undo it or stop it, depending on which version of Excel you’re using.
For all Excel versions where dates are automatically grouped, Excel 2016 and later, you can manually ungroup the dates, with one of these two methods:
After you ungroup the Date field,
Turn Off Automatic Date Grouping
For pivot tables in Excel 2019 and Excel 365, you can change an Excel Option setting, to turn this date grouping feature on or off. That will prevent date grouping in any future pivot tables.
This is option is an application-level setting, which:
To turn the automatic date grouping feature off, follow these steps:
Avoid Date Grouping in Excel 2016
For pivot tables in Excel 2016, the Option setting to disable automatic is not available.
Instead, here are two workarounds that you can try, if you want to avoid the automatic date grouping.
Workaround #1 - Two Step Date Drop
Here’s a workaround that I used in Excel 2016, to avoid pivot table date grouping.
So, you could use that 2-step process to add a date to the pivot table, to avoid the automatic date grouping, until you get Excel 2019 or Excel for Office 365.
Workaround #2 - Registry Change
The only way to turn off pivot table date grouping in Excel 2016, if you’re brave enough, is by making a change to the Windows Registry.
To make the Registry change, follow the instructions in this article by Microsoft’s Excel team.
WARNING: Before you make a change to the Registry, remember to make a backup of the registry first, and read the warning from Microsoft, at the top of that page.
Grouped Dates in AutoFilter Drop Downs
By default, when you turn on an AutoFilter, dates are grouped in the drop down list.
Video: Ungroup Dates in Filter Drop Down
Watch this short video to see the steps for turning off date grouping in an Excel filter. Written steps are below the video.
Ungroup Dates in Filter Drop Down
If you don't want the AutoFilter dates to be grouped, you can change an Excel setting, to ungroup them.
Ungroup Dates in Filter Drop Down
You can manually change a setting, to ungroup the dates in a drop down filter list.
This is a workbook level setting, so it will affect all the AutoFilters in the active workbook.
Follow these steps to turn off the Date Grouping feature in the current workbook:
Ungroup Dates With Macro
Instead of changing the date group setting manually, you can use a macro to make the change.
This will save you time, if you need to change the setting frequently. Perhaps you like the dates grouped, for some projects that you're working on, but prefer to see the full list of dates at other times..
Macro to Turn Date Grouping On or Off
This code toggles the date grouping setting, for all AutoFilters in the current workbook:
Tip: In the AutoFilter Grouping sample workbook, that you can get in the Download section, there is a worksheet button that runs the macro.
Macro Code from Your Workbook
You can copy the code below, and paste it into a regular code module in your workbook. The macro code is also in the AutoFilter sample file, that you can get in the Download section, below.
Tip: If you store the macro in a workbook that is open all the time, such as the Personal workbook, you'll be able to use the macro in any workbook.
Sub ToggleFilterDateGroup() 'changes date grouping setting ' autofilters in current workbook ActiveWindow.AutoFilterDateGrouping _ = Not ActiveWindow.AutoFilterDateGrouping End Sub
Pivot Table Grouping: Get the Food Sales sample file so you can try the Pivot Table grouping examples shown on this page. The zipped file is in xlsx format, and does not contain macros.
AutoFilter Grouping: Get a zipped Excel AutoFilter Dates workbook with sample data. The zipped file is in xlsm format, and contains the macro to change the date group setting.
Old Items: If pivot table drop downs show outdated items, see the steps to clear old items in Pivot Table, and prevent them from reappearing.
Calculated Field Count: A pivot table calculated field always uses the SUM of other fields. See this workaround to use a count in Calculated Field formulas.
Calculated Item: Videos and written steps show how to create a calculated item in an Excel pivot table. See the features, restrictions, warnings.
Last updated: July 18, 2022 7:09 PM