Rank Numbers by Numerical Value With Excel's RANK Function

Close-Up Of Numbers On Blackboard
Marco Guidi / EyeEm / Getty Images

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

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

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:

  • 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).
Screenshot of Excel ranking the number 5 with a dataset

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.

The RANK function is compatible with all version's of Excel but 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.

RANK Function's Syntax and Arguments

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

The syntax for the RANK function is:

RANK(number,ref,[order])
  • Number is number to be ranked. This can bet the actual number (see row 4 in the example above) or the cell reference to the location of 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, they 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.

The data in the Ref argument does not need to be sorted in ascending or descending order for the Number argument value to be ranked in that 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, type the following into cell F2 of the worksheet.

=RANK(C2,A2:E2,0)
Screenshot of Excel with varying Ranking examples

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 7 have the following rankings:

  • Row 2: Fourth because it is the fourth-largest number when the Ref range is ranked in descending order.
  • Row 3: Second because it is the second-smallest number when the Ref range is ranked in ascending order.
  • Row 4: Fourth because it is the 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 in the range A6 to E6.

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