[Fixed] Excel Date Filter Not Working

Date Filter is such a powerful tool that it can organize dates as well as months and years. Besides, you can easily retrieve your data based on your requirements using the Date Filter tool. But you might have faced trouble while working with this tool. In this article, we’ll introduce you to 6 effective actions that can be taken when the Date Filter is not working in Excel. So, let’s be with us.

Excel Date Filter is Not Working: 6 Reasons with Solutions

For ease of understanding, we’re going to use a Sales Report of a particular grocery store. This dataset contains the Sales Rep, Order Date, Product Name, and their corresponding Sales amount in columns B, C, D, and E respectively.

Now, we’ll apply Filter to the Order Date column. So, it would be a Date Filter. Then, we’ll see some reasons why the Date Filter isn’t working in the Excel worksheet. Besides, we’ll give the best solutions to these problems. So, let’s explore them one by one.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.

1. If Group Dates in AutoFilter Menu Is Disabled

For the first reason, we’ll talk about enabling a feature from Excel Options. So, let’s see it.
In the following image, we can see a down arrowhead symbol beside the heading Order Date. It’s the Filter button.

• At this time, click on the Filter button on cell C4.

Then, you can see all the dates in different categories.

Normally, we see them classified as months and years. But Excel isn’t showing up like this. So, there must be some problems with this. Now, how can we solve this? Don’t be tense. Let’s follow the steps below.

📌 Steps:

• At the very beginning, go to the File tab.

• From the menu, select Options at the end.

Immediately, the Excel Options window appears before us.

• Here, move to the Advanced tab.
• Then, check the box of Group dates in the AutoFilter menu.
• After that, click OK.

• Currently, click on the Filter button on cell C4 and see the Filter is showing them in the right formation.

2. When Dates Are Formatted as Text

In the same dataset, open the Date Filter on cell C4 again.

Here, we can see some dates are grouped by months and years. But some aren’t like that. Why is it happening? The reason behind this is that some dates are showing as dates, but they are actually in Text format. To verify this,

• First of all, select all the dates in the C5:C14 range.
• Then, click on the Middle Align and Center icons on the Alignment group of commands (Because they are formatted in these formatting already. Now, we are deselecting these by clicking twice).

Presently, we can see some dates are aligned to the left, and some are aligned to the right. Now, we are sure that this range contains some text. The left-aligned dates are actually text. It’s a characteristic of Text values.

We can check Text values in another way.

• At first, click on the cell of which we want to know the Format. In this case, we selected cell C5.

Then, in the Number Format box of the Number group of commands, we can see that it’s in Text format.

To solve this issue, follow the steps below.

📌 Steps:

• First of all, select all cells in the C5:C14 range.
• Secondly, click on the drop-down icon of the Number Format box on the Number group.
• Then, select Short Date from the list.

• Now, click on the Filter Button and you’ll see it showing the right result.

3. If Filter Is Not Covering All Rows

You’ll find that the Date Filter is not working well if the Filter does not cover all rows. In this case, we have a blank row in the dataset and it’s Row 9.

• Then, click on the Filter button on cell C4.
• After that, select Sort Newest to Oldest from the options.

Instantly, a Sort Warning dialog box will pop up.

• Here, choose to Expand the selection.
• Following this, click on the Sort button.

Just, the data above the blank row gets sorted. Nothing changed in the lower part.

It’s because the Filter isn’t covering all rows in the dataset. So, we’ve to apply it again. Let’s see it in action.

📌 Steps:

• At first, select cells in the C4:C14 range.
• Then, proceed to the Home tab.
• After that, click on Sort & Filter drop-down on the Editing group.
• Later, select Filter from the options.

• Following this, open the Filter and select Sort Newest to Oldest.

• Then, do the same in the Sort Warning box.

Now, all the rows belong to the sorting.

4. If Blank Rows Exist

In this section, we’ll discuss another case where there are several blank rows in the dataset.

• Here, click on the Filter Button.
• Then, select February and May.
• As usual, click OK.

The result is like the following.

There are blank rows present, which makes it uncomfortable to watch. To fix this, we should deselect the blank cells also. So, without further delay, let’s see how we do it.

📌 Steps:

• In the Filter option, deselect January, March, April, (Blanks) to see just the data for February and May months.
• Also, click OK.

Now, it’s okay.

Here, the other months along with the blank rows got hidden.

5. Presence of Merged Cells

It’ll make a problem filtering if merged cells are in the Order Date column. See the image below.

• Now, try to sort them like before.

And, Excel will show a MsgBox with the message To do this, all the merged cells need to be the same size.

We can solve this problem in a concise way. So let’s begin.

📌 Steps:

• Initially, select these two cells C6 and C10.
• Then, jump to the Home tab.
• Next, click on Merge & Center to unmerge the cells.

Now, these two cells have unmerged. And, we can apply the Filter on them easily.

• Following this, sort them like before.

And it’s working now.

6. If Worksheet Is Protected

Here, we can see a Filter button beside the Order Date heading. But it doesn’t respond while we’re clicking on it.

One reason behind this phenomenon could be the protection of the sheet. At this time, the sheet is in Protected View. So, we have to unprotect it. Let’s see the process in detail.

📌 Steps:

• Primarily, go to the Home tab.
• In the Cells group of commands, click on the Format drop-down.
• Secondarily, select the Unprotect Sheet command from the options.

And, as you can see, the Date Filter is working smoothly in the Excel sheet.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

This article explains the probable causes and fixations of the “Date Filter not working in Excel” issue in a simple and concise manner. Don’t forget to download the practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

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