RANK Function in Google Sheets: Complete Guide

Key Insights

• RANK assigns a numerical position to values within a dataset, defaulting to descending order (highest = 1), and handles ties by assigning the same rank while skipping subsequent positions
• Use RANK.AVG when you need averaged ranks for tied values (common in statistical analysis), and combine RANK with COUNTIF to create dense rankings that eliminate gaps
• Always use absolute references ($A$1:$A$10) for the data range when copying RANK formulas across cells to prevent reference drift errors

Introduction to RANK Function

The RANK function does exactly what its name suggests: it tells you where a value stands relative to other values in a dataset. Give it a number and a range, and it returns that number’s position in the pecking order.

This function powers countless practical applications. Sales teams use it to create leaderboards. Teachers rank student performance. Analysts identify top and bottom performers in datasets. Product managers rank feature requests by vote count. Any time you need to know “where does this value stand compared to others,” RANK is your tool.

The function is straightforward but has nuances that trip up users—particularly around tie handling and reference management. This guide covers everything you need to use RANK effectively in real-world scenarios.

RANK Syntax and Parameters

The RANK function follows this signature:

RANK(value, data, [is_ascending])

Parameters explained:

• value — The number you want to rank. This is typically a cell reference pointing to a value within your dataset.
• data — The range of values to rank against. This should include the value you’re ranking.
• is_ascending — Optional. Controls ranking direction. 0 or omitted = descending (highest value gets rank 1). 1 = ascending (lowest value gets rank 1).

Here’s a basic example ranking sales figures:

| A          | B      | C                              |
|------------|--------|--------------------------------|
| Salesperson| Sales  | Rank                           |
| Alice      | 45000  | =RANK(B2, $B$2:$B$6, 0)       |
| Bob        | 62000  | =RANK(B3, $B$2:$B$6, 0)       |
| Carol      | 38000  | =RANK(B4, $B$2:$B$6, 0)       |
| David      | 62000  | =RANK(B5, $B$2:$B$6, 0)       |
| Eve        | 51000  | =RANK(B6, $B$2:$B$6, 0)       |

Results:

• Alice: 4
• Bob: 1
• Carol: 5
• David: 1
• Eve: 3

Notice Bob and David both rank 1 because they share the highest sales value. This brings us to an important behavior.

Handling Ties and Duplicate Values

When RANK encounters identical values, it assigns them the same rank. But here’s the catch: it then skips the next rank(s). In our example above, Bob and David both get rank 1, so rank 2 doesn’t exist—Eve jumps straight to rank 3.

This is called “competition ranking” or “1224 ranking” (named after the pattern: 1, 2, 2, 4 when two items tie for second place).

Consider this dataset with multiple ties:

| A       | B     | C                          |
|---------|-------|----------------------------|
| Student | Score | Rank                       |
| Anna    | 85    | =RANK(B2, $B$2:$B$8, 0)   |
| Ben     | 92    | =RANK(B3, $B$2:$B$8, 0)   |
| Cara    | 85    | =RANK(B4, $B$2:$B$8, 0)   |
| Dan     | 78    | =RANK(B5, $B$2:$B$8, 0)   |
| Eva     | 92    | =RANK(B6, $B$2:$B$8, 0)   |
| Frank   | 85    | =RANK(B7, $B$2:$B$8, 0)   |
| Grace   | 70    | =RANK(B8, $B$2:$B$8, 0)   |

Results:

• Anna: 3 (tied for 3rd)
• Ben: 1 (tied for 1st)
• Cara: 3 (tied for 3rd)
• Dan: 6
• Eva: 1 (tied for 1st)
• Frank: 3 (tied for 3rd)
• Grace: 7

Notice the gaps: no rank 2, no ranks 4 or 5. Three students share rank 3, so the next available rank is 6. This behavior matters when you’re displaying rankings to users who might find gaps confusing.

RANK vs RANK.AVG vs RANK.EQ

Google Sheets provides three ranking functions. Understanding when to use each saves headaches.

RANK.EQ — Functionally identical to RANK. The “EQ” stands for “equal”—tied values get equal ranks. Use either interchangeably.

RANK.AVG — Averages the ranks that tied values would have occupied. This is standard practice in statistical analysis.

RANK — The original function. Same behavior as RANK.EQ.

Here’s a side-by-side comparison:

| A     | B     | C                              | D                                  | E                                  |
|-------|-------|--------------------------------|------------------------------------|------------------------------------|
| Name  | Score | RANK                           | RANK.EQ                            | RANK.AVG                           |
| Tom   | 88    | =RANK(B2, $B$2:$B$6, 0)       | =RANK.EQ(B2, $B$2:$B$6, 0)        | =RANK.AVG(B2, $B$2:$B$6, 0)       |
| Lisa  | 95    | =RANK(B3, $B$2:$B$6, 0)       | =RANK.EQ(B3, $B$2:$B$6, 0)        | =RANK.AVG(B3, $B$2:$B$6, 0)       |
| Mike  | 88    | =RANK(B4, $B$2:$B$6, 0)       | =RANK.EQ(B4, $B$2:$B$6, 0)        | =RANK.AVG(B4, $B$2:$B$6, 0)       |
| Sara  | 72    | =RANK(B5, $B$2:$B$6, 0)       | =RANK.EQ(B5, $B$2:$B$6, 0)        | =RANK.AVG(B5, $B$2:$B$6, 0)       |
| Jake  | 88    | =RANK(B6, $B$2:$B$6, 0)       | =RANK.EQ(B6, $B$2:$B$6, 0)        | =RANK.AVG(B6, $B$2:$B$6, 0)       |

Results:

RANK and RANK.EQ give identical results. RANK.AVG assigns 3 to the tied scores because they occupy positions 2, 3, and 4—and (2+3+4)/3 = 3.

When to use RANK.AVG: Statistical calculations, academic grading curves, any scenario where fractional ranks are acceptable and you need mathematically “fair” tie-breaking.

When to use RANK/RANK.EQ: Leaderboards, competitions, any user-facing ranking where whole numbers are expected.

Practical Applications with Combined Functions

Raw RANK output often needs enhancement for real-world use. Here are two powerful combinations.

Dense Ranking with RANK + COUNTIF

Dense ranking eliminates gaps. If two people tie for 1st, the next person gets 2nd (not 3rd). Achieve this by counting how many unique values rank higher:

| A          | B      | C                                                    |
|------------|--------|------------------------------------------------------|
| Salesperson| Sales  | Dense Rank                                           |
| Alice      | 45000  | =SUMPRODUCT(($B$2:$B$6>B2)/COUNTIF($B$2:$B$6,$B$2:$B$6))+1 |
| Bob        | 62000  | =SUMPRODUCT(($B$2:$B$6>B3)/COUNTIF($B$2:$B$6,$B$2:$B$6))+1 |
| Carol      | 38000  | =SUMPRODUCT(($B$2:$B$6>B4)/COUNTIF($B$2:$B$6,$B$2:$B$6))+1 |
| David      | 62000  | =SUMPRODUCT(($B$2:$B$6>B5)/COUNTIF($B$2:$B$6,$B$2:$B$6))+1 |
| Eve        | 51000  | =SUMPRODUCT(($B$2:$B$6>B6)/COUNTIF($B$2:$B$6,$B$2:$B$6))+1 |

Results: Bob: 1, David: 1, Eve: 2, Alice: 3, Carol: 4

No gaps. This formula counts distinct values greater than the current value, then adds 1.

Conditional Ranking with FILTER

Rank values within a subset using FILTER to create a dynamic range:

| A      | B       | C      | D                                                              |
|--------|---------|--------|----------------------------------------------------------------|
| Name   | Region  | Sales  | Rank Within Region                                             |
| Alice  | North   | 45000  | =RANK(C2, FILTER($C$2:$C$7, $B$2:$B$7=B2), 0)                 |
| Bob    | South   | 62000  | =RANK(C3, FILTER($C$2:$C$7, $B$2:$B$7=B3), 0)                 |
| Carol  | North   | 38000  | =RANK(C4, FILTER($C$2:$C$7, $B$2:$B$7=B4), 0)                 |
| David  | South   | 55000  | =RANK(C5, FILTER($C$2:$C$7, $B$2:$B$7=B5), 0)                 |
| Eve    | North   | 51000  | =RANK(C6, FILTER($C$2:$C$7, $B$2:$B$7=B6), 0)                 |
| Frank  | South   | 48000  | =RANK(C7, FILTER($C$2:$C$7, $B$2:$B$7=B7), 0)                 |

Results:

• Alice: 2 (within North)
• Bob: 1 (within South)
• Carol: 3 (within North)
• David: 2 (within South)
• Eve: 1 (within North)
• Frank: 3 (within South)

Each person is ranked only against others in their region.

Common Errors and Troubleshooting

#N/A Error

This occurs when the value isn’t found in the data range. Common causes:

• The value cell isn’t included in the data range
• Data type mismatch (text that looks like numbers vs actual numbers)
• Extra spaces in cells

Fix: Ensure your data range includes the cell you’re ranking. Use VALUE() or TRIM() to clean data if needed.

Relative Reference Drift

The most common RANK mistake. When you copy a formula down, relative references shift:

// WRONG - relative reference
=RANK(B2, B2:B10, 0)  // When copied to row 3, becomes:
=RANK(B3, B3:B11, 0)  // Data range shifted!

// CORRECT - absolute reference for data range
=RANK(B2, $B$2:$B$10, 0)  // When copied to row 3, becomes:
=RANK(B3, $B$2:$B$10, 0)  // Data range stays fixed

Rule: Always use absolute references ($) for the data range. The value parameter should remain relative so it updates as you copy.

Mixed Data Types

RANK ignores text values in the data range—it only considers numbers. If your “numbers” are actually text (common with imported data), RANK won’t work correctly.

Fix: Convert text to numbers with VALUE() or multiply by 1:

=RANK(B2*1, $B$2:$B$10*1, 0)

Summary and Quick Reference

Syntax: RANK(value, data, [is_ascending])

Quick Reference:

Key formulas:

• Basic descending rank: =RANK(A2, $A$2:$A$10, 0)
• Ascending rank (lowest = 1): =RANK(A2, $A$2:$A$10, 1)
• Dense rank (no gaps): =SUMPRODUCT(($A$2:$A$10>A2)/COUNTIF($A$2:$A$10,$A$2:$A$10))+1
• Conditional rank: =RANK(B2, FILTER($B$2:$B$10, $A$2:$A$10=A2), 0)

Remember: Lock your data range with absolute references. Choose RANK.AVG for statistics, RANK for everything else. Combine with COUNTIF or FILTER when standard ranking doesn’t fit your needs.