Excel Advanced Filter Not Working (2 Reasons & Solutions)

What Is the 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.

We have a dataset containing Apple’s state-wise sales data. Using the Advanced Filter option, we have filtered the data for California (CA) which has a sales value of more than $7,000.

Excel Advanced Filter Is Not Working: 2 Reasons and Solutions

Reason 1 – The Criteria Range Header Is Not Equal to the Parent Dataset

While applying Advanced Filter, if the column headers of the Criteria range don’t match the parent dataset, you will not get the expected result.

Solution:

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

• Open the Advanced Filter.

• Choose the desired Action. We have chosen Copy to another location option.
• Specify the List range, Criteria range, and Copy to range.
• Click OK.

• All California (CA) sales values greater than $7,000 are filtered in a different location in the active sheet.

Reason 2 – The Parent Dataset Contains an Error Value

If you apply an Advanced Filter, rows with errors will not be included in the result.

Solution:

• We have replaced the error value with valid sales data.

• Apply the Advanced Filter to the current dataset (go to Data > Sort & Filter > Advanced).

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

• You’ll get the correct results.

Things to Remember

➤ The column headers of the Criteria range should match those of the parent datasets.

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

Download the Practice Workbook

<< Go Back to Advanced Filter | Filter in Excel | Learn Excel