Chapter 6: Conditional formatting with formulas
Exercise 27: Color overdue rows
Task
Fill each whole row whose due date (column C) is before today with light red. Leave rows with no due date.
Moves 0 (target 1)6 difference(s) from the model
File
Font
Alignment
Number
Styles
Cells
A1Press and release Alt to show KeyTips (Windows). Ctrl + 1 opens Format Cells, Ctrl + Z undoes.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Rep | Due | |
| 2 | Quote | Smith | 2026/11/8 | |
| 3 | Invoice | Johnson | 2026/11/16 | |
| 4 | Minutes | Williams | 2026/11/12 | |
| 5 | Slides | Brown | ||
| 6 | Order | Jones | 2026/11/20 | |
| 7 | ||||
| 8 |
Fill each whole row whose due date (column C) is before today with light red. Leave rows with no due date.
This drill is for computer screens. On a phone you can review the steps.
- Select A2:C6
- Home > Conditional Formatting > New Rule (Alt → H → L → N)
- Choose "Use a formula to determine which cells to format"
- Enter =AND($C2<>"",$C2<TODAY()), click Format, choose light red, and click OK
Key takeaway
Empty cells count as earlier than any date, so $C2<>"" skips blanks.
See conditional formatting for details.