RANK in Excel: Rank Scores, Sales and Leaderboards
Use RANK.EQ and RANK.AVG to rank values in Excel, highest or lowest first, break ties, and build a sorted leaderboard that updates automatically.
By Ayoub Farid · · 1 min read
RANK tells you where a value stands in a list: 1st, 2nd, 3rd. Use it for sales leaderboards, class rankings and "top performer" flags.
Syntax
=RANK.EQ(number, ref, [order])- order 0 or omitted: largest = 1
- order 1: smallest = 1 (for times, costs)
RANK (without .EQ) still works for compatibility.
Example
Sales in B2:B11. In C2:
=RANK.EQ(B2,$B$2:$B$11)Fill down. Lock the range with $, or later rows rank against a shrinking list. See absolute references.
Fastest time = 1
=RANK.EQ(B2,$B$2:$B$11,1)Ties
RANK.EQ gives tied values the same rank and skips the next (1, 2, 2, 4). RANK.AVG gives the average rank (1, 2.5, 2.5, 4).
To break ties by order of appearance:
=RANK.EQ(B2,$B$2:$B$11)+COUNTIF($B$2:B2,B2)-1Top 3 flag
=IF(RANK.EQ(B2,$B$2:$B$11)<=3,"Top 3","")A leaderboard that sorts itself
In Microsoft 365 or Google Sheets:
=SORT(A2:B11,2,-1)See SORT. For just the 2nd highest value, use =LARGE(B2:B11,2).
Frequently asked questions
How do I rank numbers in Excel?
=RANK.EQ(value,$range$), with order 1 if smallest should rank first.
What's the difference between RANK.EQ and RANK.AVG?
They differ only for ties: RANK.EQ gives the top rank to all, RANK.AVG gives the average.
How do I rank without duplicates?
Add a COUNTIF tie-breaker as shown above.
Does RANK work in Google Sheets?
Yes, RANK, RANK.EQ and RANK.AVG all work.