Chapter 17: Using Formulas with Tables and Conditional Formatting
17
- 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 that we are simply
comparing our target cell (E3) with the value in the comparison cell ($B$3). 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.
=E3<$B$3 - 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. - Click the OK button once you’ve completed choosing your formatting options.
- Click the OK button twice to confirm your formatting rule back on the New
Formatting Rule dialog box.
Highlighting Values That Exist in List1 but Not List2
You’ll often be asked to compare two lists and pick out the values that are in one list but
not the other. Conditional formatting is an ideal way to present your findings. Figure 17.5
illustrates a conditional formatting exercise that compares customers from 2018 and 2019,
highlighting those customers in 2019 that are new customers, that is, those customers who
did not exist in 2018.FIGURE 17.5
You can conditionally format the values that exist in one list but not the other.