Microsoft Office Tutorials and References
In Depth Information
Specifying Advanced Filter Criteria
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:
h Homes that have at least four bedrooms
h Homes with a square footage less than 2,000
h Homes with a table price of no more than $200,000
To select the records that describe homes that have at least four bedrooms, type Bedrooms in
cell A1 and then type >=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
Greater than or equal to
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).
The text comparisons are not case sensitive. For example, se* matches Seligman, seller ,