[Fixed!] Excel Date Filter Is Not Grouping by Month (3 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

Filtering data according to particular criteria, like filtering dates by month, is one of the most popular Excel tasks. Excel’s date filter occasionally functions differently than intended, and users may discover that dates are not grouped by month. This problem can be annoying and make reporting and analyzing data more challenging. In this section, we’ll look at a few potential causes for the Excel date filter not grouping dates by month and offer solutions to help you fix the issue.

Overview image of Excel Date Filter not grouping by month


How to Fix Excel Date Filter Not Grouping by Month: 3 Common Reasons and Solutions

This article will show you the 3 most common reasons and their solutions for Excel Date Filter not grouping by month. Throughout this article, we will use a dataset of a company’s sales of some products. We have the product code, their selling date, and the company’s sales amount.

Dataset for demonstrating the reason and solution of Excel date filter not grouping by month

Using this dataset, we will demonstrate the reasons for not working date filters by month while applying an auto filter in the next part.


1. If Dates Are Not in Proper Format

The most common reason for Excel Date Filter not grouping by month is that the date input is not in Date Format. Most of the time we insert the dates in Text format and the Date Filter does not work on text input.

If the Date inputs are in Text format, then the dates will be left aligned like the image below. Moreover, if you select more than one cell of dates, you will see only the cell count in the status bar. Whereas if the inputs are in Date format, you will see SUM, MAX, and MIN in the status bar.

Showing that dates are not in proper format

Now, these dates are in Text format. So, if we add a filter on this cell range C4:C14, then we will see that there is no month-based date filtering option. There are only the dates to select for entering like the image below.

Illustrating that Excel date filter not grouping by month

Whereas, if the inputs were in Date format then after applying the Filter there will appear the month names as a filtering option.

Illustrating that the date filter is filtering by month

Solutions:

To solve this problem, we must convert the Date inputs into Date format. Follow these steps to do that:

  • First, select the cell range to filter. We selected the range C5:C14. Then go to the Data tab and select Text to Columns from the Data Tools group of commands.

Choosing the Text to Column option for changing the format of date inputs

  • Next, Convert text to Columns Wizard named dialog box will appear. Select Delimited from the Original data type options and press Next.

Selecting Delimited from the Original data type options

  • Press Next.

Setting Tab as delimiter

  • After that, select MDY date format from the Column data format options and press Finish.

Selecting column data format

  • The date inputs are formatted with Date format and as in the image we can see that the date inputs are all Right-Aligned which means they are not Text formatted anymore.

Date inputs changed into date format from text format

Now, you will be able to Filter the dates by month.


2. All Rows Are Not Selected When Grouping

Now, let us look at another common reason for dates not grouping by month. It occurs if we don’t select all the rows in the dataset while applying the Filter option. Like in the image below only cell ranges from B4:D8 have been selected while applying Filter.

Showing all the rows not selected for filtering

So, when we click on the Drop-down beside the Sales Date column, we can only see March, April, and July months as filtering options instead of all the months present in the dataset like the image below.

All months option is not available as all rows are not selected for filtering

Solutions:

Let us look at the steps to solve this problem.

  • First, ensure the selection of all the rows you want to filter. We selected cell range B4:D15, go to the Data tab, and click twice on the Filter option from the Sort & Filter group of commands.

Select all rows and apply the filter

Now, click on the drop-down option, and you will be able to see all the months’ options to filter.

All months' option being visible as all rows have been included before applying filter


3. Grouping Dates in the AutoFilter Menu Is Disabled

Another common reason for Excel Date Filter not grouping by months is the Group dates in the AutoFilter menu is disabled, which is shown in the image below. 

Showing Grouping Dates in the Autofilter Menu is disabled

Solutions:

To enable this advanced option in Excel, follow these steps:

  • First, go to the File tab in the ribbon and click on Options.

Choosing options after clicking the File tab

  • Next, the Excel Options named box will appear. Click on Advanced from the options, scroll down and find Display options for this workbook option, and click on Group dates in the AutoFilter menu from the options.
  • After that, press OK.

Enabling Group dates in the AutoFilter menu from the Advanced options

Now, that the AutoFilter menu is enabled, you will be able to filter dates by month.


How to Use Pivot Table to Filter Dates Grouped by Month Automatically in Excel

Now, we will show you how to use a Pivot Table to filter dates grouped by month automatically in Excel.

  • Select the cell range you want to filter by month. We selected range B4:D14. Then go to the Insert tab, and select From Table/Range from the Pivot Table drop-down.

Creating pivot table with selected cells

  • Next, in the PivotTable from table or range dialog box, the selected range will be shown. Next, you can choose the location where you want to place the pivot table. We selected cell B16 from the Existing Worksheet to paste the Pivot Table and press OK.

Creating pivot table in the existing worksheet

  • Now, drag Sales Date and Months into the Rows option and Sales into the Values field to form the pivot table where we can filter the data by month.

Selecting pivot table fields for creating the table

  • Now, in cell B16 you will be able to see the pivot table.

Illustrating the pivot table with the selected fields

  • Now, click on the drop-down beside Row Labels, we can see two options under Select Field where we can choose to filter by Months or Sales Date. If we choose the Months option, we can see the Months name to filter by at the bottom.

Viewing the months' option filtering the date


How to Turn Off Automatic Date Filter Grouped by Month in Excel

If you do not want to filter dates automatically in Excel. You can turn off the auto filtering option from Excel Options.

  • First, go to the File.
  • Select Option and the Excel Options named box will appear.
  • Click on Data, and click on Disable automatic grouping of Date/Time columns in PivotTables from Data options.
  • Lastly, press OK to turn off the Automatic Date Filter.

Disabling automatic grouping of Date/Time columns in PivotTable from Data excel options


3 Other Common Reasons for Excel Date Filter Not Working

There are some other reasons for Excel Date Filter not working properly. Let us dig into them:

1. Existing Blank Rows

Excel uses empty cells in a column to indicate the limits of a data range. The Date Filter might only function properly if there are blank cells in the date column because Excel might recognize the column as a continuous range of data.

Showing the data set containing blank rows

To fix this problem, ensure no blank cells are in the date column. You can add any absent dates or remove any rows with empty cells in the date column to accomplish this.

  • Click on the drop-down of the Sales Date column and you will be able to see Blanks as an option along with the month names. Untick the Blanks option by clicking on it and then press OK.

Selecting blanks from the filtering options

  • Now, you can see that we have hidden the blank cells.

Illustration of blank cells hidden


2. Presence of Merged Cells

Another reason for the Date Filter not working might be the presence of merged cells in the filtering column.

Illustration of the presence of merged cells

Now, we want to sort them from newest to oldest. To do that click on the drop-down of the filter on Sales date and select Sort Newest to Oldest.

Selecting Sort Newest to Oldest option for filtering

But a Sort Warning will appear which will ask you to Expand the selection or Continue with the current selection. Select Expand the selection and press on Sort to make this filter work.

Appearance of Sort warning message box

Then, a message box will appear which says that for filtering all cells need to be in the same size.

Appearance of message box for making all cells of the same size

To solve this filtering problem, we will have to unmerge the cells.

  • To unmerge the merged cells, select the merged cells first. Here, we select cell C7 and then holding the Ctrl button click on cell C11. Then go to the Home tab, click on the Merge & Center drop-down, and select Unmerge Cells.

Selecting Unmerge Cells option for unmerging the merged cells

  • Now, we can sort the dates from newest to oldest and the filtered result would look like the image below.

Illustration of being able to filter after unmerging the merged cells


3. Protected Worksheet

Another common reason for Excel Date Filter not working is that the sheet might be protected.

Like in the image below, if we click on the filter option beside the sales date column nothing will happen. Also, you can see that some Editing and Cells options are unavailable which indicates the sheet is protected.

Illustration of worksheet being protected

To transform the sheet from this protected form, go to the Home tab, click on the Format option from the Cells group of command, and select Unprotect Sheet… from the Protection option.

Selecting Unprotect Sheet from Format options


Frequently Asked Questions

1. How do I group dates by Month in the Excel filter?

Select all the Date inputs, including the header in the dataset. Then, go to the Data tab and click on Filter. There will appear a drop-down in the title of the date column, you can click on the drop-down, and you will see the months as filtering options. If you have date inputs of different years, then after clicking on the drop-down, click on Date Filters, then All Dates in the Period, and choose the month for which data you want.

2. Why is the Excel date filter not showing months?

The most common reasons for this problem are that dates are not in the proper format, all rows of data are not selected, or Grouping Dates in the AutoFilter menu are disabled. We have discussed all these reasons with solutions in this article.

3. What should I do if I need help with the suggestions above?

A formula can extract the month-specific value from the date column, which you can use to group the data if none of the other solutions mentioned above are successful. For example, Excel’s “MONTH” function allows you to extract the month value from a date and use it to build a pivot table or perform other data analysis tasks.


Download Practice Workbook

You can download our practice workbook from here!


Conclusion

In conclusion, users who rely on Excel to organize and analyze data may find it frustrating that the Excel Date Filter does not group by month. However, this problem has several possible causes, such as incorrect formatting, data type, mixed data formats, grouping settings, outdated software, and blank cells in the date column. Users can successfully solve the issue and get the Date Filter to function correctly by addressing these potential problems. To prevent future issues with the Excel Date Filter, one must verify if the data is in the correct format and fulfills all the requirements.


<< Go Back to Date Filter | Filter in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Priti
Priti

Priti Halder holds a BSc degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. She has been a part of the ExcelDemy project for 6 months and during this time, she has written over 30 articles and 5 comments for the platform. Priti is currently employed as an Excel and VBA content developer and provides effective solutions to various Excel-related issues. She is passionate about expanding her knowledge of data analysis and Microsoft... Read Full Bio

2 Comments
  1. Hello,

    Using the “Convert text to Columns Wizard” method to convert the data helped. I was getting really frustrated because nothing seen on the internet seemed to work, but this method saved me.

    Thank you very much and have a great day/night!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo