Excel Filter feature helps to filter data according to your needs. It is a really helpful feature. But, sometimes it does not work properly. The objective of this article is to explain why Excel Filter is not working after a certain row.
Why Filter Is Not Working After Certain Row in Excel: 4 Reasons with Solutions
To explain this article, I have taken the following dataset. It contains the State and Sales Person. Here, I have added filters to this dataset.
In the following picture, you can see that I have applied a filter for Texas. However, the Excel Filter is not working after a certain row which is row 6. I will explain the reasons behind this problem with solutions.
Reason-01: Having Blank Cell in Range
The reason why Excel Filter is not working after a certain row can be having blank cells in the dataset.
- To check that, click on the filter.
- Now, you will be able to see where the filter is added.
- After that, click on (Select All).
- Then, select OK.
- Now, you will be able to see all the data. And, the filter was not working after the blank cell.
Solution: Select Whole Range Before Applying Filter
Here, I will explain how you can solve this problem.
- Firstly, select the cell where the Filter is added.
- Secondly, go to the Data tab from the Ribbon.
- Thirdly, click on Filter to remove it.
- After that, select the entire range with the blank cells.
- Then, go to the Data tab.
- After that, select Filter.
- After that, click on the marked button to apply Filter.
- Then, select the fields you want to filter. Here, I selected Texas.
- Next, I selected OK.
- Now. you will see the Filter is giving correct results.
Reason-02: Range Contains Hidden Cells
The other reason why Excel Filter is not working after a certain row is that the range contains hidden cells. Let’s see how you check that.
- Click on the Filter.
- Now, you will be able to see where the Filter is applied.
- After that, click on (Select All) to see all the data.
- Then, select OK.
- Here, you can see all the data. You can see that the dataset does not contain any blank cells. But, Row 8 is missing.
Solution: Unhide Hidden Cell & Select Whole Range
Now, I will show you how you can solve the problem when the Excel Filter is not working after a certain row due to hidden cells. Let’s see the steps.
- In the beginning, put your mouse cursor where the row is missing.
- Then, Right-click on your mouse.
- After that, select Unhide.
- Now, you will be able to see all the cells. And, you can see that the dataset contains a blank cell.
- First, I will remove the existing Filter.
- To do that, select the cell where the Filter is.
- Then, go to the Data tab.
- Next, select Filter.
- After that, select the whole range where you want to apply the filter.
- Then, go to the Data tab.
- Next, select Filter.
- After that, click on the Filter button.
- Then, select the field where you want to apply the filter.
- After that, select OK.
- Finally, you will see that the Excel Filter is working properly.
Reason-03: Having Merged Cells in Filtered Range
Another reason why Excel Filter is not working after a certain row can be having merged cells in the range. If you hover over the filter you will be able to see the applied filters. In the following image, you can see that the filter is applied for Florida and Texas. But, the table also contains Ohio.
- To see all the data, click on the filter.
- After that, click on (Select All).
- Next, select OK.
- Now, you will be able to see all the data. And, you can see that there is a merged cell. This is causing the problem while filtering.
Solution: Unmerge Cells
To solve this problem, you will have to unmerge the cell. Let’s see the steps.
- Firstly, select the merged cell.
- Secondly, go to the Home tab.
- Thirdly, select Merge and Center.
- After that, you will see that you have unmerged the cells.
- Then, enter data in the blank cells.
- Next, click on the Filter button.
- After that, uncheck the options you want to Filter out.
- Further, select OK.
- Finally, you can see that the Filter is working properly.
Reason-04: Adding Data After Applying Filter in Excel
In the following picture, you can see that the filter is applied for “Texas”. But, the Filter is not working after Row 13. The possible reason why Excel Filter is not working after certain row can be adding data after applying the filter.
Solution: Reapply Filter
You can solve this problem easily, by reapplying the Filter. Let’s see how you can do that.
- First, select the cell where you have applied the Filter.
- Then, go to the Data Tab.
- Next, select Reapply.
- Then, you will see that the Filter is giving you your desired output.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
To conclude, I tried to explain why Excel Filter is not working after a certain row in this article. Here, I explained 4 reasons with solutions. I hope this article was clear to you. If you have any questions, feel free to let me know in the comment section below.
<< Go Back to Not Working | Filter in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!