How to Use RANK Function in Excel (6 Ideal Examples)

The simplest technique to establish a number’s relative position in a list of numbers is to sort the list in descending (from largest to smallest) or ascending order (from smallest to largest). In this article, I’ll focus on the ways of sorting using the RANK function in Excel from different aspects.


RANK Function in Excel (Quick View)

In the following image, you can see the basics of the RANK function in Excel. It is an overview of the article which represents an application of the RANK function in Excel.

Excel RANK Function


Download Practice Workbook

Here, I have provided the practice workbook for you. You can download it from the link below.


Introduction to RANK Function

Introduction to RANK function

  • Function Objective:

The RANK function returns the position of a given number in a given list of other numbers.

  • Syntax:

=RANK (number, ref, [order])

  • Arguments Explanation:

ARGUMENT

REQUIRED/OPTIONAL

EXPLANATION

number Required The number that you want to rank.
ref Required It is the reference (an array or a list of numbers) that contains the number.
[order] Optional It is the way of ranking. 0 is used for descending order and 1 is used for ascending order.
  • Return Parameter:

It returns a rank number.


6 Ideal Examples of Using RANK Function in Excel

To explain this article, I have taken the following dataset. This dataset contains the Names of some students and their Obtained Marks. I will Rank these students based on the Obtained Marks using the RANK function in Excel. I will explain 6 ideal examples.

Dataset for Using RANK Function in Excel


1. Use RANK Function in Descending Order

In this first example, I will use the RANK function to rank the students in descending order. Let’s see how you can do it.

Steps:

  • Firstly, select the cell where you want to show the Rank. Here, I selected Cell D5.
  • Secondly, in Cell D5 write the following formula.
=RANK(C5,$C$5:$C$15,0)

Use RANK Function in Descending Order in Excel

  • After that, press Enter to get the result.

Here, in the RANK function, I selected C5 as the number, C5:C15 as the ref, and 0 as the order. Now, the formula will return the rank of the value in cell C5 among the cell range C5:C15 in descending order. I used the Absolute Cell Reference for the ref so that the formula does not change while using Autofill.
  • After that, drag the Fill Handle down to copy the formula.

Dragging Fill Handle to Copy the Formula Using RANK Function in Excel

  • Finally, you will see that you have copied the formula to all the other cells and got ranks for every student.


2. Apply RANK Function in Ascending Order in Excel

You can also rank values using the RANK function in Excel. In this example, I will show you how you can do it. Here, the formula will be the same except 1 will be used for the ascending order. Let’s see the steps.

Steps:

  • In the beginning, select the cell where you want the Rank. Here, I selected Cell D5.
  • Then, in Cell D5 write the following formula.
=RANK(C5,$C$5:$C$15,1)

Apply RANK Function in Ascending Order in Excel

  • After that, press Enter to get the Rank.

Here, in the RANK function, I selected C5 as the number, C5:C15 as the ref, and 1 as the order. Now, the formula will return the rank of the value in cell C5 among the cell range C5:C15 in ascending order. I used the Absolute Cell Reference for the ref so that the formula does not change while using Autofill.
  • Next, drag the Fill Handle down to copy the formula.

  • Here, you can see that I have copied the formula to all the other cells and got a rank for every student.


3. Employ RANK Function in Non-Contiguous Cells

Sometimes you will face a situation where you will have to rank blank cells or non-contiguous cells. In this example, I will show you how you can rank in this type of situation using the RANK function in Excel. Let’s see the steps.

Steps:

  • Firstly, select the cell where you want the Rank.
  • Secondly, write the following formula in that selected cell.
=IFERROR(RANK(C5,($C$5,$C$6,$C$9:$C$12),0),"")

Employ RANK Function in Non-Contiguous Cells in Excel

  • Thirdly, press Enter and you will get the Rank.

🔎 How Does the Formula Work?

  • RANK(C5,($C$5,$C$6,$C$9:$C$12),0): Here, in the RANK function, I selected cell C5 as the number, ($C$5,$C$6,$C$9:$C$12) as the ref, and 0 as order. The formula returns the rank of cell C5 in the ref in descending order. And, if it does not find the number in the ref range then it returns an error.
  • IFERROR(RANK(C5,($C$5,$C$6,$C$9:$C$12),0),””): Now, the IFERROR function returns an empty string if it finds any error. Otherwise, it will return the rank.
  • After that, drag the Fill Handle down to copy the formula.

  • Finally, you can see that I have copied the formula to the other cells and got my desired output.


Similar Readings


4. Get Unique Value Using Excel RANK Function

If two numbers are the same, the RANK function automatically returns a duplicate rank for the numbers. For example, if two distinct students get the same marks (see the following figure), you will find duplicate ranks for their Obtained Marks.

Getting Duplicate Value Using Rank Function in Excel

Now, I will show you how you can solve this problem and get a unique rank in this type of situation. Let me show you the steps.

Steps:

  • In the beginning, select the cell where you want the Rank.
  • Next, write the following formula in that selected cell.
=RANK(C5,$C$5:$C$15,0)+COUNTIF($C$5:C5,C5)-1

Get Unique Value Using Excel RANK Function

  • Then, press Enter and you will get the Rank.

🔎 How Does the Formula Work?

  • RANK(C5,$C$5:$C$15,0): Here, in the RANK function, I selected C5 as the number, C5:C15 as the ref, and 0 as the order. Now, the formula will return the rank of the value in cell C5 among the cell range C5:C15 in descending order.
  • COUNTIF($C$5:C5,C5): Now, in the COUNTIF function, I selected $C$5:C5 as the range and C5 as the criteria. The formula will return the number of cells in the range that match the criteria.
  • RANK(C5,$C$5:$C$15,0)+COUNTIF($C$5:C5,C5)-1: Finally, this formula sums the results it got from these 2 functions and then subtracts 1 from the summation.
  • After that, drag the Fill Handle down to copy the formula to the other cells.

  • In the end, you will see that you have copied the formula to the other cells and got your unique Rank.


5. Utilize RANK Function to Break Ties in Excel

In certain cases, you cannot apply the previous method to get a unique rank. You will need to break the ties based on secondary criteria.

Assuming that the percentage of Attendance is given for each student. In the following image, you can see the dataset contains both Obtained Marks and Attendance. If a student has more Attendance, he or she will be ahead of the other who has the same score but has less Attendance.

Utilize RANK Function to Break Ties in Excel

Let’s see how you can get the rank using a tiebreak.

Steps:

  • First, select the cell where you want the Rank based on the primary criteria.
  • Then, write the following formula in that selected cell.
=RANK(C5,$C$5:$C$15,0)

Using RANK Function in Excel to get Rank BAsed on Primary Criteria

  • Next, press Enter to get the Rank.

Here, in the RANK function, I selected C5 as the number, C5:C15 as the ref, and 0 as the order. Now, the formula will return the rank of the value in cell C5 among the cell range C5:C15 in descending order. I used the Absolute Cell Reference for the ref so that the formula does not change while using Autofill.
  • After that, drag the Fill Handle down to copy the formula to the other cells.

  • Next, you can see that I have got a Rank for every student.

  • After that, select the cell where you want to get the Tie Break. Here, I selected Cell F5.
  • Then, in Cell F5 write the following formula.
=IF(COUNTIF($C$5:$C$15,C5)>1,RANK(D5,$D$5:$D$15,1)/100,0)

Calculating Tie Break Using RANK function in Excel

  • Next, press Enter to get the result.

🔎 How Does the Formula Work?

  • COUNTIF($C$5:$C$15,C5): Here, in the COUNTIF function, I selected cell range C5:C15 as the range and cell C5 as the criteria. The formula returns the number of cells in the selected range that match the given criteria.
  • RANK(D5,$D$5:$D$15,1): Now, in the RANK function, I selected cell D5 as the number, D5:D15 as the ref, and 1 as the order. The formula ranks the values in ascending order.
  • RANK(D5,$D$5:$D$15,1)/100: Here, the result we got from the RANK function is divided by 100.
  • IF(COUNTIF($C$5:$C$15,C5)>1,RANK(D5,$D$5:$D$15,1)/100,0): Finally, the IF function checks if the value it got from COUNTIF is greater than 1. If the logical_test is True then it goes into the RANK function. Otherwise, it returns 0.
  • After that, drag the Fill Handle down to copy the formula to the other cells.

  • Here, you can see that I have copied the formula to all the cells and got my desired output.

  • Next, I will determine the Final Rank from the Rank and the Tie Break.
  • To do that, select Cell G5.
  • Then, in Cell G5 write the following formula.
=E5+F5

Calculating Final Rank in Excel

  • Next, press Enter to get the result.

Here, the formula returns the summation of value in cells E5 and F5.
  • After that, drag the Fill Handle to copy the formula to the other cells.

  • Finally, you can see that I have copied the formula to all the cells and got the Final Rank using a Tie Break.


6. Apply RANK Function Ignoring Zeros in Excel

In this example, I will show you how you can Rank values ignoring zeros. Here, I have taken the following dataset for this example. This dataset contains Month and Profit. The negative profits mean a loss and zeros mean a breakeven. I will use the Excel RANK function to rank the Profits ignoring the zeros.

Apply RANK Function Ignoring Zeros in Excel

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want the Rank. Here, I selected Cell D5.
  • Secondly, in Cell D5 write the following formula.
=IF(C5=0,"",IF(C5>0,RANK(C5,$C$5:$C$16,0),RANK(C5,$C$5:$C$16,0)-COUNTIF($C$5:$C$16,0)))

  • Thirdly, press Enter to get the result.

🔎 How Does the Formula Work?

  • RANK(C5,$C$5:$C$16,0): Here, the RANK function returns the Rank of cell C5 in cell range C5:C15 in descending order.
  • COUNTIF($C$5:$C$16,0): Now, in the COUNTIF function, I selected cell range C5:C15 as the range and 0 as the criteria. The formula will return the number of cells that matches the criteria.
  • RANK(C5,$C$5:$C$16,0)-COUNTIF($C$5:$C$16,0): Here, the formula will subtract the result it got from the COUNTIF function from the result it got from the RANK function.
  • IF(C5>0,RANK(C5,$C$5:$C$16,0),RANK(C5,$C$5:$C$16,0)-COUNTIF($C$5:$C$16,0)): Now, the IF function will check if the value in cell C5 is greater than 0. If the logical_test is True then it will return the result from the RANK function. Otherwise, it will return the result from the RANK and the COUNTIF function.
  • IF(C5=0,””,IF(C5>0,RANK(C5,$C$5:$C$16,0),RANK(C5,$C$5:$C$16,0)-COUNTIF($C$5:$C$16,0))): Finally, this IF function will check if the value in cell C5 is 0. If the logical_test is True then the formula will return an empty string. Otherwise, it will go to the second IF function.
  • After that, drag the Fill Handle down to copy the formula.

  • Here, you can see that I have copied the formula to the other cells and got my desired output.


Common Errors While Using RANK Function in Excel

Common Errors When They Show
#N/A It occurs when the given number that you want to find rank is not available in the reference (the list of numbers).

Things to Remember

  • Microsoft warns that the RANK function may not be available in the future as they developed new and better functions for ranking with better accuracy and usage.
  • If you omit the order (as it is an optional argument) while inserting the RANK function, the function will sort automatically in descending order.

Conclusion

So, you have reached the end of my article. I tried to cover different uses of the RANK function in Excel. If you have an interesting and unique method of using the RANK function, please share it in the comments section below.

Thanks for being with me.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo