The RANK function ranks a number compared to other numbers in a given data set. The rank has no relation to the number’s position in the list. Here’s how to rank numbers in Excel.

Example of the RANK Function

The image below shows an example of the RANK function in action. For the series of values 1, 6, 5, 8, and 10 in rows 2 and 3, the number 5 has a rank of:

Instructions in this article apply to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

  • 4 because it is the fourth-largest number in the list (see row 2).2 because it is the second-smallest number in the list (see row 3).

Neither ranking matches its position as the third value from either end. However, a number’s rank will match its position in a list if the list is sorted to match the order of ranking.

RANK Function’s Syntax and Arguments

The function syntax refers to the way the function is stated and includes the function name, brackets, comma separators, and arguments.

The RANK function is compatible with all Excel versions. However, Microsoft is phasing it out in favor of RANK.AVG and RANK.EQ. The size of RANK.AVG and RANK.EQ is relative to other values in the list. When more than one value has the same rank, RANK.AVG returns the average rank and RANK.EQ returns the top rank of the set of values.

The syntax for the RANK function is:

  • Number is number to be ranked. This can be the actual number (see row 4 in the example above) or the cell reference to the location of the data (see rows 2 and 3).
  • Ref is 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, these are ignored. In row 5 of the example, the number 5 is ranked first because it is the largest of the two numbers in the list.
  • Order is a numeric value that determines whether the Number argument is ranked in ascending or descending order. Set the Order to 0 or omit it to rank in descending order. Nonzero values rank in ascending order.

Enter RANK Functions in Excel

Since Excel 2010, the RANK function cannot be entered using the dialog box. Instead, it must be entered manually. In this case, enter =RANK(C2,A2:E2,0) into cell F2 of the worksheet:

The data in the Ref argument doesn’t need to be sorted in ascending or descending order for the Number argument value to be ranked in that order.

This simple formula references cell C2 as the number to be ranked (first argument), specifies cells A2 to E2 as the range (second argument), and sorts in descending order (third argument).

The Number argument 5 in rows 2 to 6 have the following rankings:

  • Row 2: Fourth. It is the fourth-largest number when the Ref range is ranked in descending order.Row 3: Second. It is the second-smallest number when the Ref range is ranked in ascending order.Row 4: Fourth. It is the fourth-largest number when the Ref range is ranked in descending order.Row 5: First. It is the larger of the two numbers when the Ref range is ranked in descending order.Row 6: #N/A. The number 5 is not in the range A6 to E6.

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

Get the Latest Tech News Delivered Every Day