Spreadsheet Practice

Lesson 9 of 32, Data tools

Conditional formatting with formulas

A formula rule colors exactly the cells you mean, and keeps up when the data changes.

Free10 minutes, 3 exercisesConditional formattingCOUNTIF$

Watch it done

4 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

The built-in rules (greater than, top 10, duplicate values) cover simple cases. For anything else, select the cells, then go to Home > Conditional Formatting > New Rule and choose Use a formula to determine which cells to format. Type a formula that returns TRUE for the cells to color, click Format to pick a fill, and press OK.

Write the formula for the first cell of your selection, the active cell. If you selected C2:C15, the rule =C2>500 is about C2. Excel then moves it the way it moves any formula you fill down: C3 checks C3, C4 checks C4.

That is why the $ signs matter. Point at a settings cell with both dollar signs, =C2>$E$2, so every row compares against the same limit. To color whole rows, select every column and lock only the column of the cell you test: =$C2<$F$2. Each cell in a row then looks at that row's column C.

COUNTIF makes a duplicate rule: =COUNTIF($B$2:$B$13,B2)>1 is TRUE when a value appears more than once in the locked list. The practice grid below the sheet works like the New Rule dialog and lights up the matches as you type.

Syntax

=C2>$E$2Above the limit in E2, for every row
=$C2<$F$2Whole row, when column C is before the date in F2
=COUNTIF($B$2:$B$13,B2)>1Values that appear more than once
=$D2="Overdue"Whole row, by the text in column D

Watch out for

  • Write the rule for the first cell you selected. A rule written for row 1 on a selection that starts at row 2 is off by a row.
  • No $ on the settings cell and the rule slides past it: C3 compares to E3, C4 to E4, and most of those are empty.
  • Typing the number into the rule (=C2>500) works until the limit changes. Point at a cell instead.
  • The formula must return TRUE or FALSE. A rule like =C2 colors every cell that isn't zero.

The exercises

  1. Over the limit. Highlight the claims in C2:C15 that are over the approval limit in E2. Write the rule for C2 and point at the limit cell, so a new limit recolors the list.
  2. Overdue rows. Color every row of A2:D13 whose due date in column C is before the report date in F2. The rule applies to all four columns, so write it for A2, the first cell of the range.
  3. Repeat customers. Highlight the emails in B2:B13 that appear more than once in the list. Use COUNTIF, and write the rule for B2.

Over the limit

Highlight the claims in C2:C15 that are over the approval limit in E2. Write the rule for C2 and point at the limit cell, so a new limit recolors the list.

New Formatting Rule

Applies toC2:C15
Rule typeUse a formula to determine which cells to format

Write it for the first cell, C2. Matching cells light up as you type.