Ungroup Dates in Excel Filter Drop Down

By default, when you turn on an AutoFilter, dates are grouped in the drop down list. In the screen shot below, the dates have been rolled up to the years, 2013 and 2014.


Maybe you like the group dates feature, because it creates a short list of years, and you can drill down to find the date that you want.


Change the Setting

Sometimes you might prefer to see the full list of dates, instead of digging through each folder, to find the dates that you want. The good news is that you don’t have to put up with those grouped dates.

You can manually change an Option setting, to ungroup them, or use programming to turn the grouping on or off.

Manually Turn Date Grouping Off

Follow these steps to turn off the Date Grouping feature in the current workbook:

  1. On the Ribbon, click the File tab, then click Options
  2. Click the Advanced category
  3. Scroll down to the Display Options for This Workbook section
  4. Remove the check mark from Group Dates in the AutoFilter menu.
  5. Click OK to apply the setting change.


Ungroup Dates in Filter Drop Down

When you change the setting, it only applies to the current workbook. So, if you frequently change the grouping option, you can use programming to turn the grouping on or off. Put the following code into your Personal Workbook, or another file that is always open, then add a button for it on the Quick Access Toolbar.

This code toggles the date grouping setting — if the grouping is on, it turns it off, and if grouping is off, the code turns it on.

Sub ToggleFilterDateGroup()
    ActiveWindow.AutoFilterDateGrouping _
    = Not ActiveWindow.AutoFilterDateGrouping
End Sub