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.
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.
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.
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.
Whereas, if the inputs were in Date format then after applying the Filter there will appear the month names as a filtering option.
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.
- Next, Convert text to Columns Wizard named dialog box will appear. Select Delimited from the Original data type options and press Next.
- Press Next.
- After that, select MDY date format from the Column data format options and press Finish.
- 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.
Now, you will be able to Filter the dates by month.
Read More: [Fixed] Excel Date Filter Not Working
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.
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.
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.
Now, click on the drop-down option, and you will be able to see all the months’ options to 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.
Solutions:
To enable this advanced option in Excel, follow these steps:
- First, go to the File tab in the ribbon and click on Options.
- 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.
Now, that the AutoFilter menu is enabled, you will be able to filter dates by month.
Read More: How to Use Custom Date Filter in Excel
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.
- 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.
- 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.
- Now, in cell B16 you will be able to see the pivot table.
- 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.
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.
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.
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.
- Now, you can see that we have hidden the blank cells.
2. Presence of Merged Cells
Another reason for the Date Filter not working might be the presence of merged cells in the filtering column.
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.
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.
Then, a message box will appear which says that for filtering all cells need to be in 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.
- Now, we can sort the dates from newest to oldest and the filtered result would look like the image below.
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.
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.
Read more: VBA Code to Filter Data by Date in Excel
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.
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!
Hello Calin,
You are most welcome.
Regards
ExcelDemy