Part II: Working with Formulas and Functions
To build this basic formatting rule, follow these steps:
- Select the data cells in your target range (cells E4:E28 in this example).
- Click the Home tab of the Excel Ribbon and then select Conditional Formatting
➪ New Rule New Rule. This will open the New Formatting Rule dialog box shown
in Figure 17.6.
FIGURE 17.6
Configure the New Formatting Rule dialog box to apply the needed formula rule.
- In the list box at the top of the dialog box, click the option called Use a for-
mula to determine which cells to format. This selection evaluates values based on
a formula you specify. If a particular value evaluates to true, then the conditional
formatting is applied to that cell.
- In the formula input box, enter the formula shown here. Note we’re using the
COUNTIF function to evaluate whether the value in the target cell (E4) is found in
our comparison range ($B$4:$B$21). If the value is not found, the COUNTIF func-
tion will return a 0, thus triggering the conditional formatting. As with standard
formulas, you’ll need to ensure that you use absolute references so that each value
in your range is compared to the appropriate comparison cell.
=COUNTIF($B$4:$B$21,E4)=0
- Click the Format button and choose your desired formatting. This will open the
Format Cells dialog box, where you’ll have a full set of options for formatting the
font, border, and fill for your target cell.
