Spotlight the best performers — the top 10% of values — with a PERCENTILE threshold. Unlike “top 10 items,” this scales with your data’s distribution.
The example
Values in the top 10% are flagged.
| A | B | |
|---|---|---|
| 1 | Score | Tier |
| 2 | 98 | top 10% |
| 3 | 72 | — |
The formula
Threshold at the 90th percentile:
How it works
PERCENTILE sets a data-driven cutoff:
PERCENTILE(range, 0.9)returns the value below which 90% of the data falls — the top-10% threshold.- The rule
A1 >= thatCutoffhighlights everything at or above it. - Lock the range absolutely so every cell uses the same cutoff.
- Change
0.9to0.75for the top quartile,0.95for the top 5%, and so on.
Top 10% vs top 10 items. Excel’s built-in Top/Bottom Rules → Top 10% does this without a formula. The PERCENTILE formula gives you control over the exact percentile and pairs with other conditions; the built-in rule is quicker for a one-off.
Try it: interactive demo
Top 10% by value highlight.
Variations
Top quartile
Top 25%:
Bottom 10%
Flag the lowest:
Built-in rule
Top/Bottom Rules → Top 10%.
Pitfalls & errors
Lock the range. $A$1:$A$100 absolute so the cutoff is the same for every cell.
Percentile, not count. Top 10% by value can be more or fewer than 10% of cells when there are ties at the cutoff.
PERCENTILE vs .INC/.EXC. Classic PERCENTILE = inclusive; the cutoff differs slightly from PERCENTILE.EXC.
Practice workbook
Frequently asked questions
How do I highlight the top 10% of values in Excel?
How do I highlight the top 25% instead?
How is top 10% different from top 10 items?
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