Excel Date Filter is not grouping by month [Fix]

One of the most popular tasks in Microsoft Excel is filtering dates by month, or another particular criterion. However, there are times when the Excel date filter grouping does not work as intended, and as such, users may find that dates are no longer being grouped by month. This is an annoying issue for many since it can make analyzing and reporting data a challenging affair.

If the Excel Date Filter is not grouping by month, follow these suggestions to fix the issue:

1] Select all rows when grouping dates in Excel

One of the most common reasons why dates are not grouped by month is because users tend to forget to select all rows in the dataset. This is important if we wish to apply the Filter option, so let us explain.

Begin by ensuring the rows you want to filter are all selected.

From the image above, you can see we have chosen to select the B4:D15 range.

Please navigate to the Data tab, then click the Filter option twice.

You can find Filter via the Sort & Filter group.

Click the dropdown menu, and immediately you will see all the months available for filtering.

READ: Excel theme not applying

2] Enable grouping dates in the AutoFilter menu

If the Grouping Dates option in the AutoFilter menu is not enabled, then you will not have the ability to group Excel date filters by months. Let us explain what you need to do.

Navigate to the File tab, and select Options from the Ribbon.

From there, you will see Excel Options.

Look to the left menu pane, and select Advanced.

Next, you must scroll down until you come across Display options for this workbook.

The next thing to do here is to tick the box that reads, Group dates in the AutoFilter menu.

Hit the OK button, and from now on the AutoFilter menu will be enabled and ready for use.

READ: Autocomplete is not working in Excel

3] Set dates in the proper format

The most common reason why the Date Filter in Excel fails to group together is due to improper formatting. Sometimes we insert dates in Text format, and for those unaware, the Date Filter does not work with text.

To solve this issue, we must move to convert all Date inputs to Date format. Let us explain how.

The first thing to do is to select the cell range you wish to filter.

After that, you must click on the Data tab, and then choose Text to Columns via the Data Tools grouping.

In a moment, the Convert text to Columns Wizard dialog box will appear.

From that box, select Delimited, then hit the Next button.

Press Next once more.

You now need to click MDY date format via the Column data format.

Click the Finish button when you’re done.

As it stands right now, the date inputs have all been formatted with Date format.

Hope this helps.

READ: External Data Connections have been disabled in Excel

How do I sort data by month in Excel?

Go ahead and click on the Data tab, then select the Sort button. From the dialog box, click on the column with the month numbers. Look under Order and choose Smallest to Largest, and that’s it.

How do I convert a date to a month in Excel?

First, select the Date, then left-click on the required cell. After that, select the format cell option, then Custom. Type mmmm, and right away the date will be converted to the respective month.