Microsoft Office Tutorials and References
In Depth Information
More About the Criteria Range
The comparison criteria in the criteria range determine the records that will pass the test
when the Advanced Filter dialog box is used. This section describes examples of different
If the criteria range contains a blank row, it means that no comparison criteria have
been deﬁ ned. Thus, all records in the table pass the test. For example, the blank row in
the criteria range shown in Figure 5–62 means that all records will pass the test.
all records will
pass test because
criteria deﬁ ned
If the criteria range contains two or more entries below the same ﬁ eld name, then
records that pass either comparison criterion pass the test. For example, based on the cri-
teria range shown in Figure 5–63, all records that represent sales reps with a State value of
MT or NM will pass the test.
State = MT
or State = NV
The Criteria Area
When you add items in
multiple rows to a criteria
area, you must redeﬁ ne
the range of the name
Criteria before you use
it. To redeﬁ ne the name
Criteria, click the Name
Manager button on the
Formulas tab on the
Ribbon. When Excel
displays the Name
Manager dialog box,
select Criteria in the list
and then click the Delete
button. Next, select the
new Criteria area and
name it Criteria using
the Name box.
If an AND operator applies to the same ﬁ eld name (Age > 50 AND Age < 55),
then you must duplicate the ﬁ eld name (Age) in the criteria range. That is, add the ﬁ eld
name Age in cell K2 to the right of Grade and then adjust the range assigned to the name
Criteria by using the Deﬁ ne Name command on the Formulas tab on the Ribbon.
When the comparison criteria below different ﬁ eld names are in the same row, then
records pass the test only if they pass all the comparison criteria. If the comparison criteria
for the ﬁ eld names are in different rows, then the records must pass only one of the tests.
For example, in the criteria range shown in Figure 5–64, female sales reps OR outside
sales reps pass the test.