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 sim-
ply referencing the first cell in our target range. There is no need to reference the
entire range.
=C3<4000
Note in the formula that we exclude the absolute reference dollar symbols ($) for the target cell (C3). If you click cell
C3 with your mouse instead of typing it in, Excel will automatically make your cell reference absolute. It’s important
that you don’t include the absolute reference dollar symbols in your target cell, as you need Excel to apply this for-
matting rule based on each cell’s own value.- 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.
If you need to edit your conditional formatting rule, simply place your cursor in any of the data cells within your for-
matted range and then go to the Home tab and select Conditional Formatting ➪ Manage Rules. This will open the
Conditional Formatting Rules Manager dialog box. Click the rule that you want to edit and then click the Edit Rule
button.Highlighting cells based on the value of another cell
In many cases, the formatting rule for your cells will be based on how they compare to the
value of another cell. Take the example illustrated in Figure 17.3. Here the cells are condi-
tionally highlighted if their respective values fall below the Prior Year Average value shown
in cell B3.