Site icon Professor Excel

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.

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.

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?

These two selected cells are correctly recognized as date cells.

Image by Sa Ka from Pixabay

Exit mobile version