How to Use Advanced Filter to Filter Data with Complex Criteria in Your Excel

You will always use the feature of filtering data in Excel. Today we will show you how to filter data with complex criteria by advanced filter in Excel.

Suppose now you need to filter data in the two columns.An Example for Advanced Filter

You can certainly filter data through the feature of “Sort & and Filter”. But if your criteria are complex, you can also use advanced filter in the worksheet. And we will show you how to use the advanced filter to filter data in Excel.

Filter Data with Complex Criteria

  1. Insert several rows in the worksheet.
  2. And then input the same column headers in the first row in the worksheet.Input Same Column Header
  3. Now input the criteria into the corresponding column. Suppose we need to filter the column “DataNumen Excel Repair”. And the criteria of the data in this column are: number <=179.95 or number >=359.8.Input the Criteria
  4. And then click the tab “Data” in the ribbon.
  5. Next click “Advanced” in the toolbar.Click Advanced
  6. And then in the “Advanced Filter” window, input the list range and the criteria range into the corresponding textbox.Advanced Filter
  7. Next click the button “OK” in the window. Thus, you will see the result immediately in the worksheet.Filter Result

Here the criteria are still a little simple. If you need to filter data with more complex criteria, you need to make a little adjustment. And you can continue to see the next part of this article.

Examples for Other Criteria

Criteria 1:

Now we need to sort the values of the column “DataNumen Excel Repair” with the criteria: number >=179.95 and number <=359.8. If you directly input the criteria in the same column, nothing will happen when you filter data. You may follow the steps below to see how it works.

  1. Right click the cell C1 in this example.
  2. And then click the option “Insert” in the menu.Insert
  3. In the “Insert” window, choose the option “Shift cells right”.
  4. And then click “OK”. Thus, you have inserted a cell in the first row.Shift Cells Right
  5. Now type the character “DataNumen Excel Repair” in the new cell.
  6. And then input the two criteria into the two columns with the same header “DataNumen Excel Repair”.Same Column Header
  7. Now repeat the step 4-7 of the first part to filter the worksheet.Filter Again

And then you will get the right result that you need.The Right Result

From the above example, we know that if you need to meet one of the criteria, you can input the criteria in the same column. But if both need to be fulfilled, you need to input the criteria in different column with the same header.

Criteria 2:

Now let’s see how the advanced filter takes effect for two different columns. Here we use these criteria: the data of “DataNumen Excel Repair” is less than or equals to 179.95 and the data of “DataNumen Access Repair” is greater than or equals to 399.9. We need to meet both the criteria.

  1. Now input the two criteria into the corresponding columns.Input the Criteria
  2. And then repeat the step 4-7 of the first part to filter the worksheet. Thus, you can get the result like the image below show.

Result of Two Criteria

Criteria 3:

Now, we make a little change of the criteria 2. The data of “DataNumen Excel Repair” is less than or equals to 179.95 or the data of “DataNumen Access Repair” is greater than or equals to 399.9. You only need to meet one criterion for the same row. Thus, you need to input one criterion into another row in the column. You can refer to the image below.Move to Another Row

Here we input the second criterion into cell C3. And then you can use the advanced filter with the same steps.Result of Criteria 3

From all of the above examples, now we can make such a conclusion: if you only need to meet one of the criteria for the same row, you need to input the different criterion into different rows with their corresponding column header. On the other hand, if you need to meet all the criteria for the same row, you need to input them in the same rows in the list range. When there are two or more criteria for the same column, you need to add a new column with the same column header.

The Difference between Ordinary and Advanced Filter

Actually you can directly set the criteria in the “Custom AutoFilter” window. Thus, in this part, we will analyze the difference.

  1. Click one cell in the target range.
  2. And then click the button “Sort & Filter” in the toolbar.
  3. Next click the option “Filter” in the submenu.Sort and Filter
  4. Now click the small arrow in column header of the target column.
  5. And then move your cursor on the option “Number Filters”.
  6. In the new menu, choose the option “Custom Filter”.Custom Filter

Thus, you will see the window of “Custom AutoFilter”. Here you can also set the criteria for the columns separately.Custom AutoFilter

We will use the Criteria 3 as an example: The data of “DataNumen Excel Repair” is less than or equals to 179.95 or the data of “DataNumen Access Repair” is greater than or equals to 399.9. When you set the criteria for the two columns, you will get the same result as the Criteria 2 instead of the result of Criteria 3.Same Result as Criteria 2

And that is exactly the difference of the two different filter features in Excel. Hence, the next time if you need to filter data with complex criteria, you can choose one of the features. The choice is totally depend on your actual need.

Recovery Tool is Essential for Everyone

Nowadays, the results of virus and malware have become more and more serious. Thus, you need to prepare a recovery tool at hand. Among all the victims, Excel takes a large part due to its importance. Our qualified staffs have already developed a recovery tool. It is able to repair xls or other Excel files in a short time. Therefore, you will never worry about your Excel files.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.