Excel Advanced Filter Not Working (2 Reasons & Solutions)

In this article, I will explain why the Advanced Filter feature is not working in certain situations in Excel. Basically, the Advanced Filter is the advanced version of the regular filter feature of Excel. Ironically, this is not much used as other features like regular Excel filters.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


What is Advanced Filter in Excel?

We can use the Advanced filter to perform complex filtering in Excel. For example, you can filter data based on complex criteria; such as user-defined criteria. Usually, a basic Excel Filter will filter the existing dataset. On the other hand, using the Advanced Filter, you can extract the filtered data set to a new location. You can even filter unique records from an existing dataset.

For example, we have a dataset containing Apple’s different product’s state-wise sales data. Now, using the Advanced Filter option we have filtered data for California (CA) which has a sales value of more than $7000.

What is Advanced Filter in Excel?

Related Content: Excel Advanced Filter [Multiple Columns & Criteria, Using Formula & with Wildcards]


2 Reasons & Solutions Regarding Excel Advanced Filter Not Working

Reason 1: Criteria Range Header is Not Similar to the Parent Dataset

While applying Advanced Filter, if the column headers of the Criteria range are not similar to the parent dataset, you will not get the expected result. In such situations, if you apply Advanced Filter to the existing dataset, the following will be the result.

Advanced Filter Not Working If Criteria Range Header is Not Similar to the Parent Dataset

Solution:

  • First, rectify the column headers of both the Criteria range and parent dataset. You can copy the column headers from the parent dataset to the criteria range.

Advanced Filter Not Working If Criteria Range Header is Not Similar to the Parent Dataset

  • Next, go to Data > Sort & Filter > Advanced.

Advanced Filter Not Working If Criteria Range Header is Not Similar to the Parent Dataset

  • Now, the Advanced Filter dialog box will show up. Then, choose the desired Action first. I have chosen the Copy to another location option. After that, specify List range, Criteria range, and Copy to one by one. Click OK after you specify all the parameters.

Advanced Filter Not Working If Criteria Range Header is Not Similar to the Parent Dataset

  • Finally, the following will be the result. All California (CA) sales values greater than $7000 are filtered in a different location in the active sheet.

Related Content: Applications of Advanced Filter Criteria Range in Excel


Reason 2: Excel Parent Dataset Contains Any Error Value

Sometimes, datasets contain error values.  In such cases, if you apply Advanced Filter corresponding rows will not be included in the result. As a result, you will not get the desired result. In that case, you have to delete or rectify the faulty value.

If Excel Parent Dataset Contains Any Error Value Advanced Filter Not Working

Solution:

  • In my case, firstly, I have replaced the error value with valid sales data.

If Excel Parent Dataset Contains Any Error Value Advanced Filter Not Working

  • Secondly, I have applied the Advanced Filter to the current dataset. To do that, go to Data > Sort & Filter > Advanced.

If Excel Parent Dataset Contains Any Error Value Advanced Filter Not Working

  • Thirdly, choose the Action and specify List range, Criteria range and Copy to. Then click OK.

  • In conclusion, you will see the following result. As we have replaced the error value with $9000, an extra row is added to the filtered result.


Things to Remember

➤ The column headers of the Criteria range should be exactly the same as those of the parent datasets.

➤ Remember, Advanced Filtering cannot be undone if you choose the Copy to another location action.


Conclusion

In the above article, I have tried to discuss why Advanced Filter does not work sometimes and suggested probable solutions too. Hopefully, these solutions and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo