Excel Not Grouping Dates in Filters? How to Fix It!

Filters (or AutoFilters) are very powerful in Excel: Not only allow them the basic filtering, but also sorting, filtering by colors and much more. One aspect about the filters I like the most: Gaining a quick overview of the data in the column below. When it comes to dates, filters group dates by year, month and day. That’s very helpful for gaining an overview of the date ranges as well as quickly applying filters. But what, if the filter has suddenly stopped to grouping dates? Here are possible reasons and how to fix it!

Initial comments to the grouping dates issues in filters

A few comment before we start:

By default, dates are grouped by year, month and day in Excel. Grouping Dates.
By default, dates are grouped by year, month and day in Excel.
  • The following reasons are listed in the order of how simple and fast it is to apply them. Typically, the last reason is most probably, but before we start a larger investigation, let’s make sure that the “quick fixes” are finished.
  • If some dates are grouped and some are not in your filters, jump right to reason 3 below.
  • If you want to know more about filters, please refer to this article.

Reason 1: Grouping dates in filters is disabled

The easiest thing to check if is grouping dates is activated within the Excel options.

First step: Grouping dates in Excel options activated?
  1. In Excel, go to File.
  2. Click on Options (usually in the left bottom corner of the screen).
  3. Go to the Advanced tab in the left pane of the Options window).
  4. Scroll down to the workbook settings and set the check at “Group dates in the AutoFilter menu”.

Back in your filter, is the grouping working now?

Reason 2: Your filter is not covering all rows to group dates

This second option is more like a quick side note: Make sure that your filter covers all items in your list or table.

To be sure, why don’t you remove it and insert it again? Here is how to do that.


Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!


Reason 3: Excel does not recognize the dates as dates

Unfortunately, to my experience this is the most common reasons when Excel does not group dates in filters any longer. Excel does not recognize the dates as dates.

Dates recognized as text and not dates.
Dates recognized as text and not dates.

You could check this first: What does the summary in the status bar show when you select multiple (more than one, two are enough) date cells?

  • If it only say “Count: 2” it’s most probably a text cell (see screenshot on the right side). In this case you have to convert the cells to dates. Here is how to do that (see the section of forcing text to number formats or the universal format at the end).
These two selected cells are correctly recognized as date cells.
These two selected cells are correctly recognized as date cells.
  • If the status bar also shows a summary of the dates (with at least one of the following values: minimum, maximum, sum, numerical count or average), both selected cell are recognized as dates. In this case you should not have any problems (at least with these two cells) and check reasons 1 and 2 above.

Image by Sa Ka from Pixabay

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *