Rank Largest to Smallest in Excel and Google Sheets
Purpose
Ranking values from largest to smallest is one of the most common analytical operations in any performance report, leaderboard, or…
Ranking values from largest to smallest is one of the most common analytical operations in any performance report, leaderboard, or competition table. Who is the top-performing sales rep this quarter? Which product has the highest return rate? Which region generated the most revenue? The RANK function answers all of these in a single formula: give it a value, the full range of values to rank against, and 0 for descending order, and it returns that value's position from the top. Lock the range with $ signs before copying down and every row gets its own correct rank. The main behaviour to understand is how RANK handles ties -- it gives tied values the same rank and skips the next number. For leaderboards where every position must be unique, the RANK plus COUNTIF combination produces sequential ranks that break ties by row order. Both patterns are covered below.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=RANK(number, ref, [order])
RANK.EQ is the modern equivalent in Excel 2010+. Use RANK.AVG to average tied ranks.
Arguments
| Argument | Required | Description |
|---|---|---|
| number | Required | The value to rank. Usually a cell reference like B2. |
| ref | Required | The full range of values to rank against. Must be locked with $ signs when the formula is copied: $B$2:$B$11. |
| order | Optional | 0 (default) for descending rank (largest = 1). 1 for ascending rank (smallest = 1). |
How it works
RANK compares the number argument against every value in the ref range and returns its position. With order 0 (descending), the largest value gets rank 1. With order 1 (ascending), the smallest value gets rank 1. When two values are equal, both receive the same rank -- the lower of the two positions they would occupy -- and the next rank is skipped. If two values tie for positions 3 and 4, both receive rank 3 and no value receives rank 4. This means the total count of ranks used equals the total count of values, but individual rank numbers may not be sequential. The ref range must be locked with $ signs: =RANK(B2,$B$2:$B$11,0). Without the $ signs, the range shifts as the formula copies down and ranks become wrong. RANK.EQ (Excel 2010+) is identical to RANK. RANK.AVG gives tied values the average of the ranks they would occupy. For unique sequential ranks, add COUNTIF($B$2:B2,B2)-1 to the RANK result -- this adds an offset based on how many times the value has already appeared.
Examples
1
Rank 10 sales reps from highest to lowest sales so the top performer receives rank 1.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Rank |
| 2 | Alice | 4200 | =RANK(B2,$B$2:$B$11,0) |
| 3 | Bob | 3100 | 9 |
| 4 | Carol | 5500 | 1 |
| 5 | Dan | 3800 | 7 |
| 6 | Ella | 4900 | 3 |
| 7 | Finn | 5100 | 2 |
| 8 | Gia | 2800 | 10 |
| 9 | Hugo | 4700 | 4 |
| 10 | Iris | 3600 | 8 |
| 11 | Jo | 4100 | 6 |
Row 2: 5
— Alice sold 4200. Carol at 5500 is rank 1, Finn 5100 is rank 2, Ella 4900 is rank 3, Hugo 4700 is rank 4, and Alice at 4200 is rank 5. Gia at 2800 is last at rank 10.
The ref argument $B$2:$B$11 is locked with $ signs so it remains fixed when the formula copies down. Without the $ signs, B2:B11 shifts to B3:B12 for the second row, B4:B13 for the third, and rankings become completely wrong.
2
Handle tied values -- two reps with the same sales figure -- showing how RANK.EQ and RANK.AVG differ in their treatment of the tie.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | RANK.EQ | RANK.AVG |
| 2 | Alice | 5500 | =RANK.EQ(B2,$B$2:$B$7,0) | =RANK.AVG(B2,$B$2:$B$7,0) |
| 3 | Bob | 4900 | 2 | 2 |
| 4 | Carol | 4200 | 3 | 3.5 |
| 5 | Dan | 4200 | 3 | 3.5 |
| 6 | Eve | 3800 | 5 | 5 |
| 7 | Finn | 3100 | 6 | 6 |
Row 2: 1
— Alice is rank 1 with no tie. Carol and Dan both have 4200 -- RANK.EQ gives both rank 3 and skips rank 4. RANK.AVG gives both 3.5 (the average of positions 3 and 4). Eve then receives rank 5 with both methods.
Google Sheets uses RANK not RANK.EQ but the behaviour is identical. RANK.AVG is available in Google Sheets too. Use RANK.EQ for leaderboards and RANK.AVG in statistical analysis where averaged ranks are preferred.
3
Create sequential unique ranks that break ties by row order -- no two reps receive the same rank number.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Unique Rank |
| 2 | Alice | 5500 | =RANK(B2,$B$2:$B$7,0)+COUNTIF($B$2:B2,B2)-1 |
| 3 | Bob | 4900 | 2 |
| 4 | Carol | 4200 | 3 |
| 5 | Dan | 4200 | 4 |
| 6 | Eve | 3800 | 5 |
| 7 | Finn | 3100 | 6 |
Row 2: 1
— Carol and Dan both have 4200. Standard RANK gives both rank 3. The COUNTIF addition gives Carol (appearing first) rank 3+1-1=3 and Dan (second occurrence) rank 3+2-1=4. All six ranks are now unique.
The COUNTIF range $B$2:B2 (first $ fixed, second not) expands as the formula copies down -- this is the key to counting how many times the current value has appeared so far. The -1 ensures the first occurrence gets no offset.
Common use cases
1. Rank sales reps from highest to lowest revenue to produce a leaderboard
=RANK(B2,$B$2:$B$100,0)
2. Rank exam scores from highest to lowest to assign class positions
=RANK(C2,$C$2:$C$50,0)
3. Rank from smallest to largest to find the lowest cost or fastest time
=RANK(B2,$B$2:$B$100,1)
4. Create unique sequential ranks that do not repeat when values are tied
=RANK(B2,$B$2:$B$100,0)+COUNTIF($B$2:B2,B2)-1
5. Use RANK result to dynamically sort a report using INDEX MATCH
=INDEX(A$2:A$11,MATCH(ROW()-1,$C$2:$C$11,0))
Common errors
All ranks come out the same or wrong
The ref range is not locked with $ signs. When the formula copies down, the range shifts and each row ranks against a different set of values.
Fix: Lock the ref range with $ signs: $B$2:$B$11 not B2:B11. Select the range in the formula bar and press F4 to add $ signs automatically.
Rank skips a number after tied values
This is the correct RANK behaviour. Two values tied at rank 3 both receive rank 3 and the next rank is 5, not 4. No value receives rank 4.
Fix: If every rank position must be unique, use the RANK plus COUNTIF formula to break ties sequentially.
#N/A or wrong result when the number is not in the ref range
The number argument references a cell outside the ref range, or the ref range does not include all values being compared.
Fix: Ensure the ref range covers all rows that should participate in the ranking, and that the number cell is within or comparable to that range.
Tips and variations
Always lock the ref range with $ signs
=RANK(B2,$B$2:$B$11,0) -- the $ signs before B and before the row numbers keep the range fixed when copied. Without them, each copy of the formula ranks against a shrinking range and produces nonsense results. This is the single most common RANK mistake.
=RANK(B2,$B$2:$B$11,0)
Use RANK to dynamically sort a report with INDEX MATCH
Combine RANK with INDEX MATCH to pull the nth-ranked row: =INDEX($A$2:$A$11,MATCH(ROW()-1,$C$2:$C$11,0)) retrieves the name of the rep ranked equal to the current row number. This pattern builds a fully dynamic sorted leaderboard without using the Data > Sort feature.
=INDEX($A$2:$A$11,MATCH(F2,$C$2:$C$11,0))
Use LARGE and SMALL as alternatives for specific rank positions
=LARGE($B$2:$B$11,1) returns the highest value, =LARGE($B$2:$B$11,2) the second highest, and so on. When you only need specific ranked values rather than ranks for every row, LARGE and SMALL are simpler than RANK combined with INDEX MATCH.
=LARGE($B$2:$B$11,1)
Excel vs Google Sheets
Excel vs Google Sheets
RANK works in Excel 2003 and newer and all versions of Google Sheets. RANK.EQ and RANK.AVG are available in Excel 2010+ and Google Sheets. Google Sheets uses RANK as the base function and also supports RANK.AVG. The behaviour of tied ranks is identical across all versions and applications.
Frequently asked questions
Use =RANK(B2,$B$2:$B$11,0) where B2 is the value to rank, $B$2:$B$11 is the full range of values (locked with $ signs so it does not shift when copied), and 0 specifies descending order so the largest value receives rank 1. =RANK.EQ works identically and is the modern replacement for RANK in Excel 2010 and newer. Both are available in Google Sheets.
When two or more values are equal, RANK and RANK.EQ give all tied values the same rank and skip subsequent ranks. If two values tie for rank 3, both receive rank 3 and no value receives rank 4 -- the next rank is 5. RANK.AVG instead averages the ranks the tied values would occupy: two values tying for positions 3 and 4 each receive rank 3.5.
RANK is the original function available in all Excel versions -- it behaves identically to RANK.EQ. RANK.EQ was introduced in Excel 2010 as a more explicitly named version of RANK. RANK.AVG also introduced in Excel 2010 averages the ranks of tied values rather than giving them all the lower rank. Use RANK or RANK.EQ for standard leaderboard rankings. Use RANK.AVG in statistical contexts where averaged ranks are more meaningful.
Set the order argument to 1: =RANK(B2,$B$2:$B$11,1) gives rank 1 to the smallest value. The third argument controls direction: 0 (or omitting the argument) ranks descending with the largest value as rank 1, and 1 ranks ascending with the smallest value as rank 1.
Combine RANK with COUNTIF: =RANK(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1. The COUNTIF part counts how many times the current value has appeared so far in the range from the top to the current row, making each occurrence of a tied value get a progressively higher rank. This produces sequential ranks with no gaps and no ties, though the order among tied values depends on their row order in the data.