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.
- 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.
- 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.
- 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 $.
- 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.
- 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.