RANK in Google Sheets returns a number’s position among other numbers. By default, the largest value ranks 1.
Add 1 as the third argument when the smallest value should rank first, such as a race time.
Rank Scores and Players from Highest to Lowest
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Player | Score |
|---|---|
| Maya | 85 |
| Noah | 90 |
| Cody | 95 |
| Lena | 90 |
| Omar | 75 |
| Ari | 80 |
Enter this formula in E2:
=RANK(B2,$B$2:$B$7)Maya’s score of 85 ranks 4. Filling the formula down gives ranks 4, 2, 1, 2, 6, 5. Cody’s 95 is highest, while Noah and Lena share rank 2.
The dollar signs lock the comparison range when you fill down. RANK calculates positions without sorting or rearranging your records. Keep the name column beside the scores so the results remain identifiable.
RANK Function Syntax
=RANK(value, data, [is_ascending])The value is the number to rank, and data is the comparison range. Use 0 or omit the final argument for descending ranking. Use 1 for ascending ranking.
The value must be present. =RANK(86,B2:B7) returns #N/A because 86 is absent. RANK does not insert an imaginary score into the list to calculate its position.
Rank Race Times with the Smallest First
Enter this dataset starting in A12. Leave cells marked “(blank)” empty.
| Runner | Seconds |
|---|---|
| A | 12.4 |
| B | 11.8 |
| C | 12.1 |
| D | 13.0 |
=RANK(B14,$B$13:$B$16,1)The 11.8-second time returns 1. These are numeric seconds, so a smaller value means a faster time. Without the final 1, the largest time would receive the top rank.
Understand Tied Ranks and Gaps
Enter this dataset starting in A22. Leave cells marked “(blank)” empty.
| Seller | Sales |
|---|---|
| A | 6000 |
| B | 5000 |
| C | 4500 |
| D | 4500 |
| E | 3000 |
=RANK(B25,$B$23:$B$27)Both 4500 values receive rank 3. The 3000 value ranks 5, because four records have higher sales. No record receives rank 4.
For average tied positions, =RANK.AVG(B25,$B$23:$B$27) returns 3.5, the average of positions 3 and 4. Choose the tie policy that matches your reporting requirement.
Break Ties or Use Dense Ranking
To give tied values separate positions in their existing row order, use this pattern and fill down:
=RANK(B23,$B$23:$B$27)+COUNTIF($B$23:B23,B23)-1The first 4500 remains rank 3, and the second becomes rank 4. The expanding COUNTIF range counts earlier equal values. This breaks ties by row order, not by a second performance measure.
For tied values to share a rank without leaving a gap afterward, use dense ranking:
=MATCH(B27,SORT(UNIQUE($B$23:$B$27),1,FALSE),0)The 3000 value now ranks 4. UNIQUE removes duplicate scores, SORT orders them descending, and MATCH returns the score’s position in that distinct list.
Return the Name of the Highest Scorer
=INDEX(A2:A7,MATCH(MAX(B2:B7),B2:B7,0))This returns Cody. MATCH locates the maximum score and INDEX returns the corresponding name. If multiple players share the maximum, this formula returns the first one.
For all player ranks from one formula, use =ARRAYFORMULA(RANK(B2:B7,$B$2:$B$7)) in a clear column. This article ranks numeric data; it does not collect search-engine ranking positions.
Other Google Sheets articles you may also like