[Fixed!] Advanced Filter Not Working in Excel

In excel, we can use the Filter option in several ways. Accordingly, the advanced filter is also an excellent tool for filtering data for complex criteria. However, sometimes it does not work correctly if the headers do not match or there is any error in the dataset. So, in this article, we will see four examples of the advanced filter not working in Excel with practical solutions.

📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.

What Is Advanced Filter in Excel?

As its name suggests, the Excel Advanced Filter is an improved version of the standard filter. This is useful when you filter the data set using more complex criteria.

In Advanced Filter, there is an option Action where one can choose whether to filter the list in place with this option Filter the list, in-place or copy it to another location by choosing Copy to another location option.

In addition, the advanced filter requires three pieces of information: List range, Criteria range, and, if chosen, Copy to another location, then Copy to.

List range: The cell range to filter

Criteria range: The criteria based on which the data is to be filtered

Copy to: The cell reference where to paste the filtered data

Thus, the advanced filter is different from the regular filter option:

While the regular data filter filters the existing dataset, you can also use the advanced filter for extracting the data set to a different location.

Moreover, you can use complex criteria with Excel Advanced Filter for extracting unique data records from the dataset.

Now, let us look at the practical solutions if your advanced filter is not working correctly.

Learn to Fix If Your Advanced Filter in Excel Is Not Working with These 4 Possible Solutions

First, let us look at one example of advanced filtering. Here, we have the product names, selling dates, and prices. We have filtered the iPad Pro whose price is under $600 and got three entries of this product.

Now, let us see the reasons for not getting this result and the solutions for that with these 4 scenarios.

While using the advanced filter, the listed range needs to be selected with the headers; otherwise, the advanced filter takes the first row as the header. So, when the list range header and criteria range header do not match, the advanced filter does not work.

In this example, the header in the list range is Price. On the other hand, the header in the criteria is Prices which results in the Advanced Filter not working.

So, to solve this problem, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, we must ensure that the criteria range header matches the listed range. So, change the header in the criteria. Then, go to the Data tab and select Advanced from the Sort & Filter.

• Next, a pop-up named Advanced Filter will appear. Select Copy to another location, then insert $B$5:$D$19 as List range, $F$5:$G$6 as Criteria range, and $F$10:$H$13 in Copy to option and press OK.

• Lastly, the advanced filter will work properly and show the desired result.

Another reason for the advanced filter not working is that the listed range contains an error value.

So, to solve this problem, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, we must ensure there is no error in the list range. So, change the error data into valid data. Then, go to the Data tab and select Advanced from the Sort & Filter.

• Next, a pop-up named Advanced Filter will appear. Select Copy to another location, then insert $B$5:$D$19 as List range, $F$5:$G$6 as Criteria range, and $F$10:$H$13 in Copy to option and press OK.

• Lastly, the advanced filter will work properly and show the desired result.

The advanced filter sometimes does not give proper results if any merged cell is in the listed range. So, for this type of problem, unmerge the merged cells and insert proper values in the cells, and then the filter would work accordingly.

So, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, we must ensure that there is no merged cells in the list range. So, unmerge all the merged cells. Then, go to the Data tab and select Advanced from the Sort & Filter.

• Next, a pop-up named Advanced Filter will appear. Select Copy to another location, then insert $B$5:$D$19 as List range, $F$5:$G$6 as Criteria range, and $F$10:$H$13 in Copy to option and press OK.

• Lastly, the advanced filter will work properly and show the desired result.

When filtering text values, an advanced filter sometimes does not work correctly. Here, we have some code and want to filter one code from this list. However, the advanced filter did not function correctly and only copied the listed range in the destination. The reason is that the filter did not recognize the criteria as a text value, so instead of filtering, it just returned the whole list.

So, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, in cell D6, enter ‘SC101 as the criteria instead of SC101. The advanced filter can now recognize the value as a text value.

• Then, go to the Data tab and select Advanced from the Sort & Filter.

• Next, a pop-up named Advanced Filter will appear. Select Copy to another location, then insert $B$5:$B$13 as List range, $D$5:$D$6 as Criteria range, and $F$5 in Copy to option and press OK.

• Lastly, the advanced filter will work properly and show the desired result.

📝 Takeaways from This Article

📌  This article described why the advanced filter not working in Excel with 4 examples.

📌  In the first example, we showed that the advanced filter does not work in Excel if the headers in the list range and criteria range do not match.

📌  Then, we demonstrated that if there is any error value in the list range, then the advanced filter will not work correctly.

📌  After that, we showed that the presence of merged cells also hampers advanced filter working.

📌  Finally, we showed how to filter text values in advanced filtering if it is not showing the expected result in Excel.

Conclusion

This article has demonstrated 4 examples of why the advanced filter is not working in Excel with practical solutions. All these solutions are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.