[Fixed] Excel Date Filter Not Working

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

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


6 Reasons with Solutions When Excel Date Filter Is Not Working

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.

excel date filter not working

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.

If Group Dates in AutoFilter Menu Is Disabled

  • 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.

Surfing to the File tab

  • From the menu, select Options at the end.

Selecting Options to fix Date Filter not working in Excel

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.

Working on Excel Options window to fix Date Filter not working

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

Fixing Date Filter not working in Excel

Read More: Excel Data Validation Greyed Out (4 Reasons with Solutions)


2. When Dates Are Formatted as Text

In the same dataset, open the date filter on cell C4 again.

When Dates Are Formatted as Text

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).

Verifying the number format of dates

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.

Checking Text Values in Date
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.

Applying Short Date format to fix Date Filter not working in Excel

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

Fixing date filter not working in Excel

Read More: [Solved]: If Macro Settings Is Greyed out in Excel


3. If Filter Is Not Covering All Rows

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

If Filter Is Not Covering All Rows

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

Sorting Newest to Oldest

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.

Data above the blank row get sorted

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.

Applying Filter to fix Date Filter not working in Excel

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

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

Sort Warning dialog box

Now, all the rows belong to the sorting.

Fixing date filter not working in Excel

Read More: [Fixed!] Excel Scrolling Too Many Rows (2 Easy Solutions)


4. If Blank Rows Exist

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

If Blank Rows Exist

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

Showing Data of February and May

The result is like the following.

showing blank rows in Date filter in Excel

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.

Deselecting Blanks to fix Date Filter not working in Excel

Now, it’s okay.

Fixing date filter not working in Excel

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

Read More: [Solved!]: Excel Page Layout Being Greyed Out (4 Quick Fixes)


5. Presence of Merged Cells

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

Presence of Merged Cells

  • Now, try to sort them like before.

Sorting newest to oldest

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.

Unmerging Cells to fix Date Filter not working in Excel

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

  • Following this, sort them like before.

Sorting Newest to Oldest in Date Filter in Excel

And it’s working now.

Fixing date filter not working in Excel

Read More: [Fixed!] Merge Cells Button Is Greyed Out in Excel


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.

If Worksheet Is Protected

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 Unprotect Sheet command from the options.

Unprotecting Sheet to fix Date Filter not working in Excel

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

Fixing date filter not working in Excel

Read More: [Fixed!] Unshare Workbook Greyed Out in Excel


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. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.


Related Articles

Tags:

Shahriar Abrar Rafid
Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo