The ready-made conditional formatting rules only look at the cell itself. A formula rule lets you colour a whole row, or compare a cell with another column. It is the skill that makes reports look professional.
Where to find it
Select your data, then Home, Conditional Formatting, New Rule, and choose Use a formula to determine which cells to format.
Highlight the entire row if status is Done
Select A2:F200. Use this formula (it is written for the first row of the selection):
=$D2="Done"
The dollar sign before D locks the column, and the missing dollar sign before 2 lets the row change. This one detail is what makes the full row colour.
Highlight overdue dates
Due date in C, status in D:
=AND($C2<TODAY(), $D2<>"Done")
Only open items with a past due date turn red.
Highlight duplicates in a column
=COUNTIF($A$2:$A$200, $A2)>1
This also works across many rows, and you can change the colour.
Alternate row shading
=MOD(ROW(),2)=0 shades every second row.
Fix a rule that colours the wrong cells
- Open Manage Rules and check Applies to.
- The formula must be written for the first cell of the range, not for the whole range.
- Check the dollar signs. A wrong lock gives a staircase of colours.