Microsoft Excel Functions & Formulas

Use the COUNTIF function to check whether a

number exists in a range

With this example you can learn how to check whether a specific

number is found in a range and have Excel automatically mark each

cell of the range that contains the number. Use the COUNTIF func-

tion to check whether the range contains the number in cell B1,

and combine it with conditional formatting to shade the specific

value as desired.

4

To check whether a number exists in a range:

1. Copy cells A1:D10 as shown in Figure 10-13, or use your

own data.

2. Select cell B1.

3. On the Format menu, click Conditional Formatting.

4. Select Formula Is and type the following formula:

=COUNTIF($A$3:$D$10,$B$1)>0 ...