Using Advanced Filter in Excel - criteria range examples with formulas

Case-sensitive filter for text values

As well as Excel AutoFilter, the Advanced Filter tool is case-insensitive by nature, meaning that it does not distinguish between uppercase and lowercase characters when filtering text values. However, you can easily perform a case-sensitive search by using the EXACT function in the advanced filter criteria.

For instance, to filter rows containing Banana, ignoring BANANA and banana, enter the following formula in the criteria range:

=EXACT(B5, "Banana")

Where B is the column containing the item names, and row 5 is the first data row.

And then, apply Excel Advanced Filter by clicking the Advanced button on the Data tab, and configure the List range and Criteria range like shown in the screenshot below. Please pay attention that the Criteria range includes 2 cells - the header cell and the formula cell.

Filter values above or below average in a column

When filtering numeric values, you may often want to display only those cells that are above or below an average value in the column. For example:

To filter rows with sub-total above average, use the following formula in the criteria range:

=F5>AVERAGE($F$5:$F$50)

To filter rows with sub-total below average, use the following formula:

=F5<AVERAGE($F$5:$F$50)

Please pay attention that we use a relative reference to refer to the top-cell with data (F5), and absolute references to define the entire range for which you want to calculate the average, excluding the column heading ($F$5:$F$50).

The following screenshot demonstrates the above average formula in action:

Those of you who are familiar with Excel Number Filters may wonder, why would someone bother to use an advanced filter while the built-in number filters already have the Above average and Below average options? That's right, but the inbuilt Excel filters cannot be used with the OR logic!

So, to take this example further, let's filter rows where Sub-total (column F) OR September sales (column E) is above average. For this, set up the criteria range with the OR logic by entering each condition on a separate row. As the result, you will get a list of items with the above average values in either column E or F:

Filter rows with blanks or non-blanks

As everyone knows, Excel Filter has an inbuilt option for filtering blank cells. By selecting or deselecting the (Blanks) check box in the AutoFilter menu, you can display only those rows that have empty or non-empty cells in one or more columns. The problem is that the built-in Excel filter for blanks can work only with the AND logic.

If you want to filter blank or non-blank cells with the OR logic, or use the blank / non-blank conditions together with some other criteria, set up an advanced filter criteria range with one of the following formulas:

Filter blanks:

Filter non-blanks:

How to extract top/bottom N records

As you probably know, the build-in Excel Number Filters have an option to display the top 10 or bottom 10 items. But what if you need to filter the top 3 or bottom 5 values? In this case, Excel Advanced Filter with the following formulas comes in handy:

Extract top N items:

Extract bottom N items:

For instance, to filter top 3 subtotals, create the criteria range with this formula:

=F5>=LARGE($F$5:$F$50,3)

To extract bottom 3 subtotals, use this formula:

=F5>=SMALL($F$5:$F$50,3)

Where F5 is the top-most cell with data in the Subtotal column (excluding the column heading).

The following screenshot shows the top 3 formula in action:

Filter for matches and differences between two columns

One of our previous articles explained a variety of ways to compare two columns in Excel and find matches and differences between them. In addition to Excel formulas, conditional formatting rules and the Duplicate Remover tool covered in the above tutorial, you can also use Excel's Advanced Filter to extract rows that have the same or different values in two or more columns. To do this, input one of the following simple formulas in the criteria range:

• Filter for matches (duplicates) in 2 columns:
  =B5=C5
• Filter for differences (unique values) in 2 columns:
  =B5<>C5

Where B5 and C5 are the top-most cells with data in the two columns you want to compare.

Filter rows based on matching items in a list

Supposing you have a big table with hundreds or thousands of rows, and you received a shorter list containing only the items relevant at a given moment. The question is - how do you find all entries in your table that are or are not in the smaller list?

Filter for weekends and weekdays

So far, our Advanced Filter criteria range examples have dealt mostly with numeric and text values. Now, it's time to give some clues to those of you who operate on dates.

The built-in Excel Date Filters provide a wide range of options that cover many scenarios. Many, but not all! For example, if you were given a list of dates and asked to filter weekdays and weekends, how would you go about it?

As you probably know, Microsoft Excel provides a special WEEKDAY function that returns the day of the week corresponding to a given date. And it is this function that we are going to use in the Excel Advanced Filter criteria range.