Ungroup Dates in Excel Filter Drop Down

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. Here’s how you can ungroup dates in Excel filter drop down lists, in the active workbook.

Automatic Grouping

This screen shot shows how dates are automatically grouped by year, in the drop down list.

dates are automatically grouped by year
dates are automatically grouped by year

Some people might 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.

dategrouping02

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.
Group Dates in the AutoFilter menu option
Group Dates in the AutoFilter menu option

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 a regular module in 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

_____________________

2 thoughts on “Ungroup Dates in Excel Filter Drop Down”

  1. Very clear description.

    A caveat. Unless I’m misunderstanding, AutoFilterDateGrouping is a setting for an Excel window, not a workbook. Unless a second window is opened for a workbook, the distinction between window and workbook does not matter.

    I briefly tested (with Office 365) on a workbook where the active sheet had a date column included in an autofilter range. After setting AutoFilterDateGrouping to False, clicking on the filter dropdown arrow of the date column showed ungrouped dates. Then a second window was opened. In the second window, the filter selection box showed grouped dates, meaning AutoFilterDateGrouping defaulted to True for that window, while the first window filter selection box continued to show ungrouped dates. When filters in other sheets in the first window were selected, they also showed ungrouped dates.

    I confirmed the different settings for each window by looking at the workbook display options for each of them.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.