Highlight the Highest and Lowest Values

Excel Formulas › Conditional Formatting

All versionsMAX/MIN

Mark the winner and the loser automatically. Two formula rules using MAX and MIN highlight the largest and smallest values in a range — and they move as the data changes.

Quick formula: select B2:B20, then add two formula rules:
=B2 = MAX($B$2:$B$20) // highest (green) =B2 = MIN($B$2:$B$20) // lowest (red)
Each cell is compared to the range’s max and min. The matching cell(s) get the format.

Functions used (tap for the full reference guide):

MAXMIN

The example

Scores with the top and bottom flagged.

AB
1NameScore
2Ann95 (max)
3Bo72
4Cy58 (min)
5Di81

The formula

Two rules — one for the high, one for the low:

=B2 = MAX($B$2:$B$20) =B2 = MIN($B$2:$B$20) // 95 → green, 58 → red

How it works

Compare each cell to the range’s extreme:

  1. Select B2:B20, then add a formula rule =B2 = MAX($B$2:$B$20) with a green fill for the highest value.
  2. Add a second rule =B2 = MIN($B$2:$B$20) with a red fill for the lowest.
  3. The range is locked ($) so every cell compares to the same MAX/MIN; the test cell is relative so it walks the column.
  4. Edit any number and the highlights jump to the new extremes automatically.

Top 3 instead of just the max? Use LARGE: =B2 >= LARGE($B$2:$B$20, 3) highlights the three highest. For the bottom 3, swap in SMALL(…, 3) with <=.

Try it: interactive demo

Live demo

Edit the scores; max and min highlight.

Variations

Top 3 values

Use LARGE:

=B2 >= LARGE($B$2:$B$20, 3)

Bottom 3 values

Use SMALL:

=B2 <= SMALL($B$2:$B$20, 3)

Max per row

Highlight each row’s best (lock columns):

=B2 = MAX($B2:$E2)

Pitfalls & errors

Ties highlight together. If two cells share the max, both light up — usually fine, but be aware if you expect exactly one.

Lock the range. $B$2:$B$20 must be absolute, or each row compares to a shifting window and the wrong cells highlight.

Blanks and text. MAX/MIN ignore text and blanks, but a stray 0 counts as the minimum — clean the data if 0 means “no value.”

Practice workbook

📊
Download the free Highlight the Highest and Lowest Values practice workbook
A scores sheet with max/min rules, the top-3, bottom-3, and per-row variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I highlight the highest and lowest values in Excel?
Add two formula rules: =B2 = MAX($B$2:$B$20) for the highest and =B2 = MIN($B$2:$B$20) for the lowest, each with its own fill color.
How do I highlight the top 3 values?
Use LARGE: =B2 >= LARGE($B$2:$B$20, 3) highlights the three largest values. For the bottom 3, use SMALL with <=.
Why do two cells highlight as the maximum?
They're tied for the highest value, so both match the MAX and get formatted. That's expected behavior.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Highlight top N · Nth largest value · Max if criteria

Function references: MAX · MIN