Conditional Formatting to catch outliers instantly
A column of five hundred numbers hides its problems. Conditional Formatting surfaces them — blanks, negatives, duplicates, outliers — the moment you apply a rule.
The quick wins
Home → Conditional Formatting, then:
- Highlight Cell Rules → Greater Than / Less Than / Equal To / Duplicate Values. Flag negatives with "Less Than → 0," or catch missing data with "Equal To → (blank)."
- Top/Bottom Rules → top 10 items, bottom 10%, above average. Good for spotting the deposits or expenses that need a second look.
- Color Scales / Data Bars → a heat map across a range so big and small values read at a glance.
Flag the whole row, not just the cell
The powerful version uses a formula rule. Select your data, choose New Rule → Use a formula, and enter something like:
=$C2<0
Lock the column with $C but leave the row unlocked, and Excel lights up the entire row wherever column C is negative. Swap in any test: =$E2="" for rows missing a value, =$D2>10000 for large amounts.
Keep it honest
Conditional Formatting is visual, not structural — it doesn't change values or stop bad input (for that, see Data Validation). And too many overlapping rules get slow and unreadable; a few sharp rules beat a rainbow.