Highlight the Top N Values

Excel Formulas › Conditional Formatting

All versionsLARGECF rule

To shade the highest few values in a list — top 3 sales, best 5 scores — use a conditional-formatting formula rule built on LARGE. Excel’s built-in “Top 10” preset works too, but a formula gives you full control.

Quick formula: select the data, add a formula rule, and enter:
=B2>=LARGE($B$2:$B$10, 3)
LARGE(range, 3) is the 3rd-largest value; any cell at least that big is in the top 3 and gets the format.

Functions used (tap for the full reference guide):

LARGERANK

How it works

The rule shades every value in the top 3. Here the helper column shows the TRUE/FALSE the rule produces.

ABC
1RepSalesIn top 3?
2Ana$120FALSE
3Ben$300TRUE
4Cy$180TRUE
5Dot$90FALSE
6Eve$250TRUE

The formula

The conditional-formatting rule (applied to B2:B6):

=B2>=LARGE($B$2:$B$6, 3) // top 3 = 300, 250, 180

How it works

The rule compares each value to the nth-largest:

  1. LARGE($B$2:$B$6, 3) returns the 3rd-largest value — 180. The range is locked so every cell compares to the same threshold.
  2. For each cell, B2 >= 180 is TRUE for the three biggest values, FALSE for the rest.
  3. Conditional formatting shades the cells where the rule is TRUE — the top 3.
  4. Change the 3 to any N, or point it at a cell to make the count adjustable.

Built-in shortcut: Home → Conditional Formatting → Top/Bottom Rules → Top 10 Items (set it to 3). Quick, but the formula rule handles ties and custom counts more precisely.

Try it: interactive demo

Live demo

Choose N; the top values light up.

Variations

Highlight the bottom N

Use SMALL instead:

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

Top N within each group

Combine with a group condition (helper or COUNTIFS rank).

Above average

A related favorite:

=B2>AVERAGE($B$2:$B$10)

Pitfalls & errors

Lock the range, not the cell. Use $B$2:$B$10 (fully locked) inside LARGE so every cell compares to the same threshold; the leading B2 stays relative.

Ties can highlight more than N. If two values tie at the threshold, both are shaded — you might see 4 cells for “top 3.” Usually fine, but worth knowing.

Rule applies to the selection. Whatever was selected when you created the rule is its range — check Manage Rules → Applies to if it looks off.

Practice workbook

📊
Download the free Highlight the Top N Values practice workbook
A sales list with a live TRUE/FALSE helper for the top-N rule, the bottom-N and above-average variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I highlight the top N values in Excel?
Use a conditional-formatting formula rule: select the data and enter =B2>=LARGE($B$2:$B$10, 3) to shade the top 3. Lock the range inside LARGE so all cells compare to the same threshold.
How do I highlight the bottom N values?
Use SMALL instead of LARGE: =B2<=SMALL($B$2:$B$10, 3) shades the three smallest values.
Is there a built-in way without a formula?
Yes: Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items, then set the count. The formula rule gives more control over ties and custom counts.

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: Sum the top N values · Rank values (no gaps) · Highlight rows with a formula

Function references: LARGE · RANK