Select the full table, then Home → Conditional Formatting → New Rule → Use a formula. Lock the column with $ but not the row, so every cell checks its own row.
Rule: overdue tasks
A3fx=AND($C2<>"Done",$B2<TODAY())
A
B
C
1
Task
Due
Status
2
Send invoices
2 Nov 2026
Done
3
Payroll check
9 Nov 2026
Open
4
Budget review
20 Nov 2026
Open
5
Vendor audit
5 Nov 2026
Open
Shown as of 12 Nov 2026. Rows turn red when they’re past due and not done.
Colour scales for spotting trends
A two-colour scale from white to green turns a grid of numbers into a heatmap. Use a single hue: red-to-green scales are hard to read for colour-blind colleagues.
Monthly sales heatmap
A1fx.
A
B
C
D
E
1
Rep
Jan
Feb
Mar
Apr
2
Asha
4,200
5,100
6,100
6,900
3
Ben
3,100
2,800
3,500
4,600
4
Chen
5,800
6,400
5,200
7,100
5
Dana
2,400
3,300
4,100
3,900
Catch duplicates before they cause trouble
Rule: duplicate IDs
A2fx=COUNTIF($A$2:$A$6,A2)>1
A
B
C
1
Order ID
Customer
Amount
2
SO-1041
Contoso
1,200
3
SO-1042
Fabrikam
640
4
SO-1043
Northwind
980
5
SO-1042
Fabrikam
640
6
SO-1044
Tailspin
310
Rule type
Use it for
Formula rule
Whole-row highlights and anything that depends on another column
Colour scale
Trends across a grid: sales by month, scores by question
Data bars
Comparing sizes inside a single column
Duplicate values
IDs, emails, invoice numbers that must be unique
Top/Bottom 10
Quick leaderboards and outliers
Try it in a live spreadsheet
Practise these formulas in interactive lessons with instant grading.
Marco Ruiz 3 min readData
Sara Lind
Lena Okafor