RANK tells you the position of a value in a list, such as a salesperson's place in a leaderboard.
Syntax
=RANK(number, ref, [order])
- order 0 (or left out): highest value gets rank 1.
- order 1: lowest value gets rank 1.
Example
=RANK(B2, $B$2:$B$50, 0)
Lock the range with $ so it stays fixed when you copy the formula down.
RANK.EQ and RANK.AVG
RANK.EQ is the same as RANK. RANK.AVG gives tied values the average of their ranks (two values tied for 2nd both get 2.5).
Break ties
With RANK, two equal values share a rank and the next rank is skipped. To make each rank unique, add a COUNTIF tie-breaker:
=RANK(B2, $B$2:$B$50) + COUNTIF($B$2:B2, B2) - 1
Rank within a group
=COUNTIFS($A$2:$A$50, A2, $B$2:$B$50, ">"&B2) + 1
This ranks each value only among rows with the same group in column A.
Sort a leaderboard
Use SORT in modern Excel, or sort by the rank column.
Worked example
Scores in B2:B6 are 90, 85, 90, 70, 60. =RANK(B2,$B$2:$B$6,0) gives 1 for 90. The second 90 also gets 1, and 85 gets 3, because rank 2 is skipped for the tie. Use 1 as the last argument for ascending order.
Common mistakes
- Not locking the range with $, so it moves when copied down.
- Expecting no gaps after ties. Ties share a rank and the next rank is skipped.
- Using the old RANK in new files. RANK.EQ gives the same result.
Frequently asked questions
How do I break a tie in RANK?
Add COUNTIFS: =RANK(B2,$B$2:$B$6)+COUNTIF($B$2:B2,B2)-1.
How do I rank within a group?
Use COUNTIFS: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,">"&B2)+1.
RANK.EQ or RANK.AVG?
RANK.EQ gives tied values the top rank. RANK.AVG gives them the average rank.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: RANK, COUNTIF, LARGE.
Start Excel practice → Case studies
