Creating a criteria range for excel advanced filters

YOUR OBJECTIVE

You want to filter your customer orders table by more complex criteria. For instance, you want to display all records for the customer, “Basiler’s United Garages”, where the orders were sent out by mail or where the order value was greater than € 1,000. Excel advanced filters can help you. The filter results will be displayed in a range outside the original table so that it can be saved as part of the file.

The custom filter reaches its limits, because it only allows you to

  • combine two criteria within one column using an AND function, i.e. using more than two criteria linked with an AND function is not possible,
  • apply an OR function within the same column, i.e. OR functions across several columns are not possible,
  • display the resulting table only within the original table, i.e. displaying the results in a different cell range or a different worksheet is not possible.

Use the exel advanced filter for this task, which, however, requires that you define the filter criteria outside the table in a separate section.

This learning module will show you how to prepare for the advanced filter by creating a criteria range.

HOW TO USE EXCEL ADVANCED FILTER?

Before using the excel advanced filter  you need to, unlike the AutoFilter, define a criteria range. You need to create a criteria table outside the table you want to filter. The number of columns in the criteria table corresponds to the number of filter criteria. It is of utmost importance that the columns carry the exact same headings as the columns of the table you want to filter. Enter the filter criteria in the rows below.

To filter the table by customer name, order value and shipping method, you have to set up three columns labeled “Customer”, “Order Value” and “Shipment” in the criteria table. You can enter these in the cells L1, M1 and N1.

Note that when entering the filter criteria, all criteria entered in the same row are linked with an AND function, while all criteria entered in different rows are linked with an OR function:

This means that “Basiler’s United Garages” should be entered in cell L2 and “Mail” in cell M2, while the “>1000″ criterion, which is to be linked by an OR function, should be entered in cell N3.

Excel advanced filters – comparison operators for numerical values:

Generally speaking you can use the following comparison operators for numerical values:

Operator Meaning Example Explanation
= equals =1000 all values that correspond to the value of 1000
< is less than <1000 all values below 1000
> is greater than >1000 all values above 1000
<= is less than or equal to <=1000 all values up to and including 1000
>= is greater than or equal to >=1000 all values above and including 1000
<> does not equal <>1000 all values except 1000 

But you can also use formulas, e.g. = 10%*1000.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen