Shahriar Abrar Rafid
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.
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.
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.
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.
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.
- 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.
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.
- 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.
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.
- 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.
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.
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.
And, as you can see, the Date Filter is working smoothly in the Excel sheet.
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.
