Using the Excel RANK Function


The Excel RANK function returns the rank of a number in a list of numbers. Rank is determined by a number's size relative to other values in a set.

RANK is available as a compatibility function and has been replaced by RANK.AVG and RANK.EQ.


=RANK (number, ref, [order])

Syntax Breakdown

Required. The number whose rank you wish to find.

Required. Array, or reference to, a list of numbers. Non-numeric values are ignored.

Optional. How you want to rank the numbers. The options include:

0 (zero) or omitted, the function ranks number as if ref was sorted in descending order.

Any non-zero number, the function ranks number as if ref was sorted in ascending order.

Usage Notes

RANK returns the rank of a number when compared to other numbers in a list. The function can rank from smallest to largest as well as largest to smallest. It isn't necessary to sort the numbers in the list prior to using RANK.

If a list of numbers includes duplicates, the duplicate numbers are given the same rank. This will impact the ranking of subsequent numbers. For example, if a list, sorted in ascending order, includes the number 5 twice with a rank of 3, then 6 would be ranked 5th. No number would be ranked 4th.

The Excel RANK Function Tie