Excel RANK Function

of 01

Rank Numbers by Numerical Value in Excel

Rank Numbers in a List with the RANK Function in Excel 2007
Rank Numbers in a List with the RANK Function in Excel 2007. © TEed French

The RANK function ranks the size of a number compared to other numbers in a list a data. The rank has no relation to the number's position in the list.

For example, in the image above, for the series of values

1, 6, 5, 8, 10

 in rows two and three, the number 5 has a rank of:

  • 4 - as the fourth largest number in the list - row two;
  • 2 - as the second smallest number in the list - row three

Neither ranking matches its position as the third value from either end.

A number's rank will match its position in a list if the list is sorted to match the order of ranking. 

The RANK Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the RANK function is:

= RANK ( Number, Ref, Order )

Number - the number to be ranked. This can be:

  •  the actual number - row four above;
  • the cell reference  to the location of data - rows two and three.

Ref - the array or range of cell references pointing to the list of numbers to use in ranking the Number argument.

If non-numeric values are present in the range, they are ignored - row five above, where the number 5 is ranked first because it is the largest of the two numbers in the list.

Order - a numeric value that determines whether the Number argument is ranked in ascending or descending order.

  • descending order - largest to smallest: - If Order is 0 (zero) or omitted;
  • ascending order (smallest to largest) - If Order is any nonzero value, such as 1.

Note: the data in the Ref does not need to actually be sorted in ascending or descending order for the Number argument value to be ranked in that order.

RANK Function Example

In the image above, the RANK function is located in cells B7 to E7 and shows the ranking for the number 5 relative to the other numbers in each column.

Entering the RANK Function

Since Excel 2010, the RANK function cannot be entered using the function's dialog box, like most other functions in the program.

To enter the function it must be entered manually - such as


into cell F2 of the worksheet.

    Interpreting the Results

    The Number argument 5 in rows two to seven has the following rankings:

    • Row 2: fourth - because it is fourth largest number when the Ref range is ranked in descending order;
    • Row 3: second - because it is second smallest number when the Ref range is ranked in ascending order;
    • Row 4: fourth - because it is fourth largest number when the Ref range is ranked in descending order;
    • Row 5: first - because it is larger of the two numbers when the Ref range is ranked in descending order;
    • Row 6: #N/A - because the number 5 is not located in the range A6 to E6.

    Ranking Duplicate Numbers

    If a list contains duplicate numbers the function gives them both same rank. Subsequent numbers in the list are ranked lower as a result.

    For example, row four contains duplicate number 5's, both are ranked third, while the number one is ranked fifth - there is no fourth ranked value.

    Rank Function since Excel 2010

    In Excel 2010, the RANK function was replaced by the:

    RANK.AVG - Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned.

    RANK.EQ - Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned.