If you want to find where a number sits in a list, like the position of a score, a sales figure, or a race time, the RANK function in Google Sheets gives you that position in one step.
You hand it a value and the list to compare against, and it tells you the rank. In this article, I’ll walk you through how RANK works with a few practical examples.
RANK Function Syntax in Google Sheets
Here is how the RANK function is structured.
=RANK(value, data, [is_ascending])
- value is the number you want to rank.
- data is the range of numbers to rank it against.
- is_ascending is optional. Leave it out or use 0 for descending order, where the largest value ranks 1. Use 1 for ascending order, where the smallest value ranks 1.
When to Use RANK Function
- Rank test scores or sales figures from highest to lowest.
- Rank race times where the smallest time should come first.
- See how a single value stacks up against a column of others.
- Spot ties, since equal values share the same rank.
Example 1: Rank Test Scores From Highest to Lowest
Let’s start with the default, ranking from largest down to smallest.
Below is the dataset with six test scores in column A, across rows 2 to 7.
The goal is to give each score its rank in column B, with the top score at position 1.
Here is the formula:
=RANK(A2,$A$2:$A$7)
The first argument is the score in A2. The second is the full list, locked with dollar signs so the range stays put when you fill the formula down.
With no third argument, RANK sorts descending, so the highest score gets rank 1. The score of 95 in row 4 is the highest, so it returns 1.
Example 2: Rank Race Times With Smallest as First
Sometimes the smallest number should win, like a race time.
Below is the dataset with six race times in column A, across rows 2 to 7. Lower is faster here.
The goal is to rank the times so the fastest runner sits at position 1.
Here is the formula:
=RANK(A2,$A$2:$A$7,1)
The third argument is 1, which switches RANK to ascending order. Now the smallest value ranks first instead of last.
The fastest time of 11.8 in row 3 returns 1. Flip that third argument back to 0 and the slowest time would rank first instead.
Example 3: Handle Tied Sales With Duplicate Ranks
Ties are worth understanding before you trust your ranks.
Below is the dataset with five sales figures in column A, across rows 2 to 6. Two of them are equal.
The goal is to rank the sales from highest to lowest and see what happens with the tie.
Here is the formula:
=RANK(A2,$A$2:$A$6)
The two 4500 values are tied, so they share the same rank. Each one returns 3, since two values sit above them.
RANK then skips the next position, so no value gets rank 4. The list jumps straight from the shared rank of 3 to rank 5 for the next-lowest figure.
Pro Tip: RANK skips a position after a tie, so a tie at rank 3 means there is no rank 4. If you want ties to count as one and keep the next rank sequential, add a tie-breaker with COUNTIF or use a dense-rank formula instead.
Example 4: Rank Each Player Against a Reference List
Last, let’s rank values that live next to a label column.
Below is the dataset with player names in column A and their points in column B, across rows 2 to 7.
The goal is to rank each player’s points in column C, with the highest scorer at position 1.
Here is the formula:
=RANK(B2,$B$2:$B$7)
RANK only looks at the points column, so the name column in A is just there for context. The value B2 is ranked against the locked range B2 to B7. If you later want to pull the name that holds a given rank, the LOOKUP function pairs well with this setup.
Cody’s 95 in row 4 is the top score, so it returns 1. Fill the formula down and every player gets a rank next to their name.
Tips & Common Mistakes
- Lock the data range with dollar signs, like
$A$2:$A$7, before filling down. Without that, the range shifts row by row and your ranks go wrong. - Remember the third argument controls direction. Leave it out for descending, where the largest ranks 1, or use 1 for ascending. Mixing this up flips every rank.
- Ties share a rank and the next position is skipped. If you need a clean 1, 2, 3 sequence with no gaps, look at the MATCH function or a COUNTIF-based tie-breaker.
RANK turns a column of numbers into clear positions with one formula. Drop the third argument for highest-first, add a 1 for lowest-first, and keep the range locked when you fill down. Once you know how it treats ties, the rest is straightforward.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: