Automatic Date Grouping in Excel

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

Starting way back in Excel 2007, dates have been automatically grouped into a hierarchy, such as years and months, when you turn on an AutoFilter or create a named Excel table.

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:

• Excel AutoFilters
• Excel pivot tables (in newer versions).

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.

Implementing grouping for Data Model PivotTables allows for grouping to be used in conjunction with the power of the xVelocity engine and is a key feature for making Data Model PivotTables a replacement for native ones in the future.

When used in a data model PivotTable, Time grouping adds relevant Date/Time columns such as Date (Year), Date (Quarter) and Date (Month) to the grouped table in the model; these columns could then be reused with other user endpoints of the data model, such as PowerView and Power BI

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.

• “You cannot place a field that has more than 16384 items in the column area. If you want to use this field in the report, click OK, and then move the field to either the row area or page 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:

Keyboard Shortcut

• Add the Date field to the pivot table
• Immediately press Ctrl + Z, to undo the automatic date grouping
• The grouped fields are removed, and the original Date field remains in the pivot table

Ungroup Command

• Add the Date field to the pivot table
• At any time, right-click on any of the date field items or headings
• In the right-click menu, click Ungroup

After you ungroup the Date field,

• grouped fields and their columns are removed
• original Date field remains in the pivot table

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.

• NOTE: See the next section for an Excel 2016 workaround.

This is option is an application-level setting, which:

• will affect all new pivot tables, in all of your Excel workbooks
• will not affect existing pivot tables

To turn the automatic date grouping feature off, follow these steps:

• On the Ribbon, click the File tab, at the far left
• In the list at the left, scroll down to the bottom
• Click the Options command
  • If you don't see Options, click More..., and then click Options
• In the list at the left, click the Data category
• At the end of the Data options section, add a check mark to "Disable automatic grouping of Date/Time columns in PivotTables"
• Click OK to apply the new settings.

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.

• First, add the date field into the Filters area
• In that part of the pivot table, the individual dates will be listed, instead of grouped dates.

• Next, move the date field from the Filter area into the Rows area

• Excel will NOT automatically group the dates when you move the field – they will stay 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, 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.

• You can leave this feature turned on, for a shorter list of dates.
  • Click the + (plus) sign at the left of a grouped date, to see its detailed list
• OR, you can turn this feature off, to show the full list of dates.

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.

• The steps in the next section show you how to change the setting manually
• OR, use the macro, further down the page, to quickly switch that setting on or off.

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:

• On the Ribbon, click the File tab, at the far left
• In the list at the left, scroll down to the bottom
• Click the Options command
  • If you don't see Options, click More..., and then click Options
• At the left, click the Advanced category
• Scroll down, about halfway, 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.

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:

• if date grouping is on, the macro turns it off
• if date grouping is off, the macro turns it on

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

Get the Sample Files

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.

More Tutorials

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.