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:
- 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.
- In Excel, go to File.
- Click on Options (usually in the left bottom corner of the screen).
- Go to the Advanced tab in the left pane of the Options window).
- 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.
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).
- 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.