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 as much used as other features like regular Excel filters.
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 multiple 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.
Excel Advanced Filter Is Not Working: 2 Reasons & Solutions
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.
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.
- Next, go to Data > Sort & Filter > Advanced.
- 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.
- 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.
Read More: How to Remove Advanced Filter 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.
Solution:
- In my case, firstly, I have replaced the error value with valid sales data.
- Secondly, I have applied the Advanced Filter to the current dataset. To do that, go to Data > Sort & Filter > Advanced.
- 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.
Read More: How to Create Dynamic Advanced Filter in Excel
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.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
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.
