RANK.EQ Function
Returns a number's rank in a list; ties share the top rank.
Category
Statistical
Available in
Excel 2010+, Microsoft 365
Returns
A whole number
Overview
RANK.EQ tells you where a value sits in a list: 1 for the largest (or, with order 1, for the smallest). Tied values get the same rank, and the next rank is skipped.
It replaces the older RANK function, which works the same way.
Syntax
=RANK.EQ(number, ref, [order])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| number | Yes | The value to rank. |
| ref | Yes | The list to rank it in. Lock it with $ when filling down. |
| order | No | 0 or left out = largest is 1; 1 = smallest is 1. |
Examples
Example 1 — Sales ranking
Ben and Gus tie for 5th, so nobody is 6th.
D2fx=RANK.EQ(C2,$C$2:$C$8)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Region | Sales | Rank |
| 2 | Ana | North | 62000 | 2 |
| 3 | Ben | South | 48500 | 5 |
| 4 | Cy | North | 50000 | 4 |
| 5 | Di | East | 71250 | 1 |
| 6 | Ed | South | 39800 | 7 |
| 7 | Fay | North | 55400 | 3 |
| 8 | Gus | East | 48500 | 5 |
=RANK.EQ(C2,$C$2:$C$8) Explain
Example 2 — Fastest is first
For times or costs, rank the smallest as 1.
=RANK.EQ(B2,$B$2:$B$20,1) Explain
Common mistakes
- #N/A The number isn't in ref.
- Forgetting the $ signs. Without $C$2:$C$8, the list shifts as you fill down and ranks come out wrong.
Notes
- To break ties, add a tiny tiebreaker: =RANK.EQ(C2,$C$2:$C$8)+COUNTIF($C$2:C2,C2)-1 ranks the first of equal values higher.
- Related functions: RANK.AVG, LARGE, PERCENTRANK.INC, SORT.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.