Conditional Formatting & Removing Duplicates¶

Conditional Formatting changes a cell's appearance based on its value — turning raw numbers into instant visual insight.

Cells colored red-yellow-green based on their values

Where to Find It¶

Home tab → Conditional Formatting

The Built-In Rules¶

Rule type	Effect
Highlight Cells	Color cells >, <, =, between, containing text
Top/Bottom	Top 10, above average, etc.
Data Bars	In-cell bar charts
Color Scales	Red→Yellow→Green heat map
Icon Sets	Arrows, traffic lights, flags

Sales column with a Color Scale:
| 1500 | 🟩 green  (high)
|  900 | 🟨 yellow (mid)
|  300 | 🟥 red    (low)

Highlighting with a Rule¶

1.Select the range.
2.Conditional Formatting → Highlight Cells Rules → Greater Than.
3.Enter 1000, pick a fill → OK.

Every value over 1000 is now colored — and it updates automatically as data changes.

Formula-Based Rules (the powerful ones)¶

Use a formula that returns TRUE/FALSE to format the whole row:

excel

3 lines

1=$D2 = "Overdue"        → highlight rows where column D is "Overdue"
2=$B2 < TODAY()          → highlight past-due dates
3=MOD(ROW(),2)=0         → manual banded rows

Tip

Lock the column with $ (e.g. $D2) but leave the row relative, so the rule reads each row's own value while highlighting the entire row.

Finding & Removing Duplicates¶

Highlight duplicates first to review them:

Conditional Formatting → Highlight Cells → Duplicate Values

Then remove them permanently:

Data tab → Remove Duplicates → choose the key columns → OK

Caution

Remove Duplicates deletes rows for good. Work on a copy, and double-check which columns define a "duplicate" — selecting only some columns can remove rows you meant to keep.