Excel RANK Function
RANK returns a number's position within a list of numbers.
Use it for scoreboards, sales leaderboards, performance bands, queue priority, or any situation where a value needs an ordinal position.
RANK syntax & arguments
Syntax
=RANK(number, ref, [order])
-
1
number
RequiredThe number whose rank should be returned.
-
2
ref
RequiredThe list of numeric values to rank
numberagainst. -
3
order
OptionalControls the ranking direction for the full list:
0— Descending: Higher values receive smaller rank numbers, so the largest value is ranked 1. This is the default when the argument is omitted.1— Ascending: Lower values receive smaller rank numbers, so the smallest value is ranked 1. Any nonzero value uses ascending order.
Example
=RANK(E2, E2:E25, 0)
Rank the value in E2 against E2:E25 with the largest value ranked first.
RANK caveats
-
Ties share the same rank
If two values tie for second, both return rank 2 and the next value returns rank 4.
-
Order changes the meaning of rank 1
Omitting
orderor using0ranks largest values first. Any nonzero order ranks smallest values first. -
The number should exist in the ref range
RANK is built to rank a value from the list. If
numberis not found inref, RANK returns a#N/Aerror instead of an inserted position. -
Non-numeric ref values are ignored
Text and blanks in
refdo not compete for rank. Error values inrefpass through, so RANK returns the error instead of a rank.
Need the ranked value instead? Use LARGE or SMALL when you know the position and need the value at that position.
Intro RANK practice problem
Solve the intro problem directly here, or open it on its own page.
Advanced RANK practice problems
Use RANK alongside other Excel functions in realistic, less-prescriptive challenges.
