Conditional formatting practice exercises

Write the formulas behind conditional formatting: a test that returns TRUE or FALSE, a $ before the column so the whole row is highlighted, overdue dates, two conditions and duplicates.

Start the first exercise

How conditional formatting formulas work

Conditional formatting colors a cell or a whole row when a rule you write is true. The rule is a formula that returns TRUE or FALSE, with no IF needed. You find it under Home, Conditional Formatting, New Rule, where you choose to use a formula. Write the formula for the first cell of what you selected, and Excel adjusts it for the rest.

=$C2>10000 highlights every row where the sales in column C are over 10,000. The $ before C locks the column, so every cell in the row tests column C, while the row number follows along down the list. For overdue invoices, =AND($B2<$G$1,$C2="No") checks the due date against today's date in G1 and that C2 says No.

Most mistakes come down to the dollar signs. Without $, the rule slides sideways and colors the wrong cells. Lock the row as well, and every row tests the same row. Dates are numbers, so you can compare them with < and >. The exercises below cover a basic test, whole rows, overdue deadlines, two conditions with AND and duplicates with COUNTIF.

  1. 1A rule is a testConditional formatting colors a cell when the rule's formula returns TRUE. In D2, write the rule for "sales over 10,000" as a test of C2, without IF.
  2. 2Highlight the whole row with $PremiumYou selected A2:C20 and want to highlight the whole row when sales are over 10,000. In D2, write the rule so every cell in the row tests column C. Lock the column with $, but not the row.
  3. 3Overdue deadlinesPremiumHighlight the row when the deadline has passed. Today's date is in G1. In D2, write the rule that tests whether the deadline in C2 is before G1. Lock column C and all of G1 with $.
  4. 4Overdue and unpaidPremiumHighlight the invoice when it is overdue and unpaid. In D2, write the rule with AND. The due date in B2 must be before G1, and C2 must say "No". Lock the columns and all of G1.
  5. 5Find duplicatesPremiumHighlight the whole row when the customer ID appears more than once in A2:A6. In D2, write the rule with COUNTIF. Lock the whole range and the column in A2.

Functions in this topic

Read the guide

Questions

How do I highlight a whole row based on one cell?

Select the whole range, such as A2:C20, then add a formula rule that locks the column with $ but not the row, for example =$C2>10000. Every cell in the row then tests column C, so the whole row gets the same color. Without the $, the rule drifts to column D and beyond, and the colors come out wrong.

How do I highlight dates that are overdue?

Compare the date with today's date. Use =$C2<TODAY() to highlight rows with a deadline before today, or point to a cell that holds the date, as in =$C2<$G$1. If you only want open items, add a second condition with AND, such as =AND($C2<TODAY(),$D2<>"Done").

How do I highlight duplicates with a formula?

Use COUNTIF with a locked range: =COUNTIF($A$2:$A$6,$A2)>1 colors every row whose customer ID appears more than once in A2:A6. Excel also has a built-in rule for duplicate values, but a formula lets you color the whole row or add more conditions.