[Fixed!] Excel Filter Not Working After Certain Row

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset for Filter Is Not Working After Certain Row in Excel

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.

Excel Filter is Not Working After Certain Row Because of Having Blank Cell in Range

  • Now, you will be able to see where the filter is added.

Clicking on Filter Button to See Where the Problem is when Excel Filter Is Not Working After Certain Row

  • After that, click on (Select All).
  • Then, select OK.

Select All to See All the Data When Excel Filter is Not Working After Certain Row

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

Select Whole Range Before Applying Filter

  • After that, select the entire range with the blank cells.
  • Then, go to the Data tab.
  • After that, select Filter.

Selecting Whole range Then Adding Filter when It is Not Working in Excel

  • After that, click on the marked button to apply Filter.

Selecting Filter Button When Excel Filter Is Not Working After Certain Row

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

Range Contains Hidden Cells

  • Now, you will be able to see where the Filter is applied.

Excel Filter Is Not Working Because Range Contains Hidden Cells

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

Unhide Hidden Cell & Select Whole Range

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

Having Merged Cells in Filtered Range

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

Unmerge Cells When Excel Filter is Not Working After Certain Row

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

Excel Filter Is Not Working After Certain Row Because of Adding Data After Applying 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.

Reapply Filter

  • Then, you will see that the Filter is giving you your desired output.

Reapplying Filter When Excel Filter is Not Working After Certain Row


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo