Microsoft Office Tutorials and References
In Depth Information
Using a Criteria Range on the Worksheet
Using a Criteria Range on the Worksheet
The AND and OR
Operators
AND means each and every
one of the comparison
criteria must be true. OR
means only one of the
comparison criteria must
be true.
You can set up a criteria range on the worksheet and use it to manipulate records that
pass the comparison criteria. Using a criteria range on the worksheet involves two steps:
1. Create the criteria range and name it Criteria.
2. Use the Advanced button on the Data tab on the Ribbon.
To Create a Criteria Range on the Worksheet
To set up a criteria range, fi rst copy the column headings in the table to another area of the worksheet. If
possible, copy the fi eld names to rows above the table, in case the table is expanded downward or to the right in
the future. Next, enter the comparison criteria in the row immediately below the fi eld names you just copied to the
criteria range. Then use the Name box in the formula bar to name the criteria range, Criteria.
The following step shows how to create a criteria range in the range A2:J3 to fi nd records that pass the test:
Gender = F AND Age > 25 AND Grade > C
A grade greater than or equal to C alphabetically means that only sales reps with grades of D and F pass the test.
1
Click the Home tab
on the Ribbon.
Copy
button
Select the
range A7:J8
and then
click the
Copy button
on the Ribbon.
Name box
criteria range
includes fi eld
names and
comparison
criteria
title identifi es
Criteria Area
grade > C
(D or F)
Click cell A1 and
then press the ENTER
key to copy the con-
tents on the Offi ce
Clipboard to the
destination
area A1:J2.
gender = F
age > 25
Change the title to
Criteria Area in
cell A1, enter F in
cell B3, enter >25
in cell C3, and then
enter >C in cell J3.
Select the
range A2:J3, click
the Name box in the
formula bar, type
Criteria as the range name, press the ENTER key, and then click cell J4 (Figure 5–57).
Must the text in the column headings in the criteria range match those in the table exactly?
Yes. To ensure the column headings in the criteria range are spelled exactly the same as the column headings in the
table, copy and paste the column headings in the table to the criteria range as shown in the previous set of steps.
Figure 5–57
 
 
Search JabSto ::




Custom Search