Excel Advanced Filter: Complex Criteria Made Easy

There is nothing otherworldly about the Advanced Filter command in Excel: it is an advanced way to filter a range.  But in the realm of online Excel forums, people speak of a mythical creature when discussing Advanced Filters: complex criteria.  Normally, an encounter with complex criteria would have me running in the other direction.  But today I’m going to face my fears, because while using the Advanced Filter is like most things in Excel (just a few clicks away), really making it work for you is a matter of understanding the varieties of complex criteria.  Ready to go pro?  Master formulas in this tutorial for Excel connoisseurs.

We need some data.  It can be pretty much anything as long as it has column labels and a minimum of three blank rows above the data; these will be used for our criteria range:

The reason you need three blank rows is because you do not enter the criteria in the Advanced Filter dialogue box; you enter it right into the worksheet, above your data.  Additionally, there must be a blank row between the criteria and the range, so that Excel can distinguish the two.

Even though we don’t enter criteria into the dialogue box, we still need to launch it.  Excel uses the Criteria range in the dialogue box as the source for the complex criteria.  The best thing to do is jump in with an example.

Criteria: Multiple Criterion in the Same or Different Columns

In my eCard business, I want to see which print cards sold more than 30 copies.  I enter the criteria in their respective columns:

Take a look at the formula bar in the above screenshot.  You’ll notice the syntax for my criteria looks like this: =”>30”.  The quotations are necessary because Excel registers an equal sign as an indication of a formula, which this clearly is not; the quotation marks indicate an equality comparison.

Now, select a cell in the criteria range.  Open the Advanced Filter dialogue box in the Data tab (located in the Sort & Filter tools group).  The range will automatically appear in the List Range box (note: this is dependent upon you first selecting a cell in the criteria range).

In the Criteria Range box, you want to enter the range that includes all of the complex criteria; in this case, Sheet1!$A$1:$D$2.  You can also select the Criteria Range box and then drag the fill handle over the desired range:

Notice the option to filter the list in place or to copy to another location.  I filtered in place:

You can accomplish the same thing with a normal filter, but I wanted to walk through the basics so that I can trim the explanations on some of the more advanced criteria…

Criteria: Multiple Sets in Multiple Columns

Say I want to see which print cards sold over $10,000 and which eCards sold over $15,000.  This is what my criteria should look like:

Make sure the criteria are in their respective rows.  For example, “print” and “>$10,000” should be in the same row, as well as “eCard” and “>$15,000”.  The List Range and the Criteria Range in the Advanced Filter dialogue box should be entered in the same manner as in the previous example:

And the result:

Does all this range talk have you dreaming of home recording?  Here’s a complete guide to professional range compression techniques in your own home.

Criteria: Intervals

To specify an interval, you use multiple criterion under the same column heading.  In my example, I am going to see which cards sold more than 40 but less than 60 copies, as well as which sold less than 30.  This requires you to make a duplicate of the column that contains multiple criterion; in this case, the “Sold” column.  Here are my criteria:

And again, we set the List range and Criteria range.  Be sure your new Criteria range includes the duplicated column:

And again, our results:

Criteria: Formulas

When using a formula as a criterion (actually, the result or value of the formula would be your criterion), remember four things:

• Drop the quotation marks
• The formula must register as either TRUE or FALSE
• Either create a new column for the formula, or enter it under a column that is not part of the criteria range
• Use a relative reference to refer to the first cell of the range in your criteria, otherwise you must use absolute references.

The following example takes all of these rules into account.  Let’s look at which cards sold less than the sum of the two worst selling cards:

No quotation marks.  The statement registers as TRUE.  I created a new column.  And I used the relative reference “C7” to refer to the list of Sold cards, and absolute references for the remainder of my formula.  The results speak for themselves:

But don’t get stuck thinking in terms of true and false.  Keep your mind sharp with these recipes for fresh thinking.

Text Values (shared characters)

Text always has a special place in my heart when it comes to Excel, this being no exception.  The classic example here would be searching through a name directory: phone book, member list, usernames, etc.  How do you filter for people with last names starting with “Jo”?  Or if you want both “Warren” and “Warran”?  Or all names that include the surname preposition “von”?  All usernames containing a “?”?

Providing examples of each would be tedious to create and to read, so here is a simple bulleted list explaining the proper syntax:

• To find text values that begin with shared characters, enter the criteria without an equal sign (=).  To find all last names beginning with “Jo”, simply enter “Jo” in the appropriate column.  This will yield such results as “Johnson,” “Jonas,” and “Joyner.”
• Use a question mark (?) to act as a single variable.  For different spellings of the same name, enter “Warr?n”.  Again, use an equal sign unless you want to filter for text values beginning with the criteria.
• An asterisk (*) signifies a group of characters.  The criterion “*von” would find, among others, “von Abel” and “von Dietrich.”
• This is common knowledge in Excel, but when it is the question mark or asterisk you are searching for, precede either with a tilde (~).

There are literally thousands of possibilities for complex criteria.  This is just a sampling to get you stoked and prime your enthusiasm for experimentation.  Have fun filtering your data at an “advanced” level, then venture into the unknown with this (truly) advanced Excel training regimen.