Excel reference

Conditional Formatting

Quick answer

Not a function, but a rule. Automatically highlights any cell in a range that meets a condition, and keeps highlighting correctly as the data changes.

Home → Conditional Formatting → Highlight Cells Rules / Top/Bottom Rules / Duplicate Values

Conditional formatting is the fastest way to make the cells that matter visually stand out, without writing a formula or touching a single value. A rule watches a range and changes how matching cells look, and it keeps re-evaluating automatically every time the underlying data changes, so there's no need to reapply it after an edit.

Only numeric cells can use Greater than, Less than, Between, or Top N, since those all compare magnitudes; Duplicate values is the one rule that works on any kind of value, text included.

Syntax

Home → Conditional Formatting → Highlight Cells Rules / Top/Bottom Rules / Duplicate Values

Argument	Description
Range	Required. The cells the rule watches. Select it first, the same way you would in Excel.
Rule	Required. Greater than, Less than, Between, Top N, or Duplicate values: LearnExcel's five most common built-in rules.
Value(s)	Required for every rule except Duplicate values: a threshold, a min/max pair, or an N.

A real example: flagging reps who beat a revenue target

A revenue column where anything over a set threshold should stand out at a glance, without a separate column reporting TRUE/FALSE:

Rule: Greater than 300, on B2:B4

Because this is a rule rather than a formula, the highlighted cells update automatically as revenue numbers change, no refresh or re-selection needed, and nothing in B2:B4 itself is altered.

Worked examples

Greater than a threshold

	A	B
1	Rep	Revenue
2	Maya	320
3	Owen	210
4	Priya	450

Rule: Greater than 300, on B2:B4 → B2 and B4 highlight (320 and 450 are both over 300; 210 is not)

Duplicate values

	A	B
1	Guest	Ticket #
2	Aya	4471
3	Ben	5820
4	Cleo	4471

Rule: Duplicate values, on B2:B4 → B2 and B4 highlight (4471 appears twice; 5820 doesn't repeat)

Common mistakes

Selecting the whole table (including label columns) instead of just the numeric column the rule should watch.
Using a fixed Greater-than cutoff when the real question is "the highest few". That's Top N, which stays correct as the data moves.
Forgetting that Between is inclusive on both ends in LearnExcel's version. A value exactly equal to the min or max still counts.

Related functions

Try it yourself

A free, graded practice exercise using this exact function: instant feedback, no signup required.

Practice Conditional Formatting →