Specifying Advanced Filter Criteria

The key to using advanced filtering is knowing how to set up the criteria range-which is the focus of the sections that follow. As you see, you have a great deal of flexibility, but you also see that some of the options are not exactly intuitive. Here you'll find plenty of examples to help you understand how to create a criteria range that extracts the information you need.

Note 

The use of a separate criteria range for advanced filtering originated with the original version of Lotus 1-2-3, more than 20 years ago. Excel adapted this method, and it has never been changed, despite the fact that specifying advanced filtering criteria remains one of the most confusing aspects of Excel. Fortunately, however, Excel's standard filtering is sufficient for most needs.

Specifying a Single Criterion

The examples in this section use a single-selection criterion. In other words, the contents of a single field determine the record selection.

Note 

You also can use standard filtering to perform this type of filtering.

To select only the records that contain a specific value in a specific field, enter the field name in the first row of the criteria range and the value to match in the second row. Figure 9-19, for example, shows the criteria range (A1:A2) that selects records containing the value 4 in the Bedrooms field.

Figure 9-19: The criteria range (A1:A2) selects records that describe homes with four bedrooms.

Note that the criteria range does not need to include all the fields from the database. If you work with different sets of criteria, you may find it more convenient to list all the field names in the first row of your criteria range.

USING COMPARISON OPERATORS

You can use comparison operators to refine your record selection. For example, you can select records based on any of the following:

• Homes that have at least four bedrooms

• Homes with a square footage less than 2,000

• Homes with a table price of no more than $200,000

To select the records that describe homes that have at least four bedrooms, enter Bedrooms in cell A1 and then enter >=4 in cell A2 of the criterion range.

Table 9-2 lists the comparison operators that you can use with text or value criteria. If you don't use a comparison operator, Excel assumes the equal sign operator (=).

Table 9-2: COMPARISON OPERATORS
Open table as spreadsheet

Operator	Comparison Type
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
< >	Not equal to

USING WILDCARD CHARACTERS

Criteria that use text also can make use of two wildcard characters: An asterisk (*) matches any number of characters; a question mark (?) matches any single character.

Table 9-3 shows examples of criteria that use text. Some of these are a bit counter-intuitive. For example, to select records that match a single character, you must enter the criterion as a formula (refer to the last entry in the table).

Table 9-3: EXAMPLES OF TEXT CRITERIA
Open table as spreadsheet

Criteria	Selects
="=January"	Records that contain the text January (and nothing else). You enter this exactly as shown: as a formula, with an initial equal sign. Alternatively, you can use a leading apostrophe and omit the quotes:
	‘=January
January	Records that begin with the text January.
C	Records that contain text that begins with the letter C.
<>C*	Records that contain any text, except text that begins with the letter C.
>=L	Records that contain text that begins with the letters L through Z.
*County*	Records that contain text that includes the word county.
Sm*	Records that contain text that begins with the letters SM.
s*s	Records that contain text that begins with S and have a subsequent occurrence of the letter S.
s?s	Records that contain text that begins with S and has another S as its third character. Note that this does not select only three-character words.
="=s*s"	Records that contain text that begins and ends with S. You enter this exactly as shown: as a formula, with an initial equal sign. Alternatively, you can use a leading apostrophe and omit the quotes: ‘=s*s
<>*c	Records that contain text that does not end with the letter C.
=????	Records that contain exactly four letters.
<>?????	All records that don't contain exactly five letters.
<>*c*	Records that do not contain the letter C.
~?	Records that contain a single question mark character. (The tilde character overrides the wildcard question mark character.)
=	Records that contain a blank.
<>	Records that contain any nonblank entry.
="=c"	Records that contain the single character C. You enter this exactly as shown: as a formula, with an initial equal sign. Alternatively, you can use a leading apostrophe and omit the quotes: ‘=c

Note 

The text comparisons are not case sensitive. For example, se* matches Seligman, seller, and SEC.

Specifying Multiple Criteria

Often, you may want to select records based on criteria that use more than one field or multiple values within a single field. These selection criteria involve logical OR or AND comparisons. Following are a few examples of the types of multiple criteria that you can apply to the real estate database:

• A list price less than $250,000, and square footage of at least 2,000

• A single-family home with a pool

• At least four bedrooms, at least three bathrooms, and square footage less than 3,000

• A home that has been listed for no more than two months, with a list price greater than $300,000

• A condominium with square footage between 1,000 and 1,500

• A single-family home listed in the month of March

To join criteria with an AND operator, use multiple columns in the criteria range. Figure 9-20 shows a criteria range that selects records with a list price of less than $250,000 and square footage of at least 2,000.

Figure 9-20: This criteria range uses multiple columns that select records using a logical AND operation.

Figure 9-21 shows another example. This criteria range displays listing that were listed in the month of March. Notice that the field name (Date Listed) appears twice in the criteria range. The criteria selects the records in which the Date Listed date is greater than or equal to March 1, and the Date Listed date is less than or equal to March 31.

Figure 9-21: This criteria range selects records that describe properties that were listed in the month of March.

Caution 

The date selection criteria may not work properly for systems that don't use the U.S. date formats. To ensure compatibility with different date systems, use the DATE function to define such criteria, as in the following formulas:

 =">="&DATE(2007,3,1) ="<="&DATE(2007,3,31) 

To join criteria with a logical OR operator, use more than one row in the criteria range. A criteria range can have any number of rows, each of which joins with the others via an OR operator. Figure 9-22 shows a criteria range (A1:C3) with two rows of criteria.

Figure 9-22: This criteria range has two sets of criteria, each of which is in a separate row.

In this example, the filtered table shows the rows that meet either of the following conditions:

• A condo with a square footage of at least 1,800

  or

• A single-family home with a table price less than $210,000

Note 

This is an example of the type of filtering that you cannot perform by using standard (non-advanced) filtering.

You can repeat a value on multiple rows to include the same criteria in two or more AND criteria. Suppose you want a condo in the Central area, but you would be willing to consider a condo in another area as long as it has a pool and at least three bedrooms. Figure 9-23 shows how you use the OR operator between the Area and the Pool and Bedrooms criteria but still limit your search to only one Type.

Figure 9-23: Repeating values in the criteria range applies the OR operator to only those criteria that aren't repeated.

Specifying Computed Criteria

Using computed criteria can make filtering even more powerful. Computed criteria filter the table based on one or more calculations. For example, you can specify computed criteria that displays only the rows in which the List Price (column D) is greater than average.

 =D9>AVERAGE(D:D) 

Notice that this formula uses a reference to the first data cell in the List Price column. Also, when you use computed criteria, the cell above it must not contain a field name. You can leave the top row blank or provide a descriptive label, such as Above Average.

By the way, you can also use a standard filter to display data that's above (or below) average.

The next computed criteria example displays the rows in which the price per square foot is less that $100. Cell D9 is the first data cell in the List Price column, and cell G9 is the first data cell in the SqFt column. The computed criteria formula is

 =(D9/G9)<100 

Figure 9-24 shows the real estate database after filtering to show only those listings that have a price per square foot less then $100. I added some formulas in column L to verify that the filtering is accurate.

Figure 9-24: Using computed criteria with advanced filtering.

Following is another example of a computed criteria formula. This formula displays the records listed within the past 60 days:

 =B9>TODAY()-60 

Keep these following points in mind when using computed criteria:

• Computed criteria formulas are always logical formulas: They must return either TRUE or FALSE.

• When referring to columns, use a reference to the cell in the first data row in the field of interest (not a reference to the cell that contains the field name).

• When you use computed criteria, do not use an existing field label in your criteria range. A computed criterion essentially computes a new field for the table. Therefore, you must supply a new field name in the first row of the criteria range. Or, if you prefer, you can simply leave the field name cell blank.

• You can use any number of computed criteria and mix and match them with noncomputed criteria.

• If your computed formula refers to a value outside the table, use an absolute reference rather than a relative reference. For example, use $C$1 rather than C1.

• In many cases, you may find it easier to add a new calculated column to your worksheet database or table and avoid using computed criteria.