Quick Navigation

RANK Function

Excel 2007+

Summary

The RANK function determines a number's position within a dataset, showing how it compares to other values. It assigns ranks based on relative size - highest values get rank 1 in descending order (default) or lowest values get rank 1 in ascending order.

Syntax

RANK(number, ref, [order])

Parameters

Parameter	Type	Required	Description
number	`Number`	Yes	The specific value to rank within the reference list
ref	`Reference`	Yes	Range or array containing the values for comparison (non-numbers ignored)
order	`Number`	No	0 or omitted = descending rank (largest=1); any nonzero = ascending rank (smallest=1)

Using the RANK Function

RANK excels at creating leaderboards, performance rankings, and position analysis. Use descending order (default) for top-performer rankings where highest scores rank #1, or ascending order for bottom-up rankings like slowest times or lowest scores.

Common RANK Examples

Sales Team Ranking (Descending)

=RANK(B2,$B$2:$B$10)

Ranks salesperson's sales figure among team. Highest sales = rank 1. Duplicates get same rank.

Test Scores Ascending Rank

=RANK(C2,$C$2:$C$20,1)

Ranks test scores ascending - lowest score gets rank 1. Use for penalty-based rankings.

Golf Scores (Low Score Wins)

=RANK(D5,$D$2:$D$15,1)

Golf scores ranked ascending since lower scores are better.

Frequently Asked Questions

Duplicates receive identical ranks, so subsequent numbers skip to maintain correct count. This is standard ranking behavior.

RANK behaves like RANK.EQ. Use newer functions for clarity, but RANK remains available for compatibility.

Non-numeric values are automatically ignored during ranking calculations.

Common Errors and Solutions

#N/A Error

Cause: Number not found in reference range

Solution: Verify number exists within ref range or use approximate matching if needed

#VALUE! Error

Cause: Invalid order argument or non-numeric inputs

Solution: Ensure order is number; ref can mix numbers/text

Unexpected rank gaps

Cause: Duplicate values shift subsequent ranks

Solution: This is normal behavior for dense ranking

Notes

Legacy function - Microsoft recommends RANK.AVG or RANK.EQ for new workbooks
Default order=0 ranks largest values highest (typical for leaderboards)
Ver. available Excel 2007+ but may be deprecated in future
Correction formula for average tied ranks: =(COUNT(ref)+1-RANK(num,ref,0)-RANK(num,ref,1))/2

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in: Excel 2003 and earlier

Content last reviewed: December 11, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+