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.

**Table of Contents**hide

## Download Practice Workbook

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

## Introduction to RANK Function in Excel

**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 | Value |
---|---|---|

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.

**Version:**

This function has been introduced in **Excel 2007 **and is available in all the later versions.

## 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.

### 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)`

- After that, press
**Enter**to get the result.

**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.

- 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)`

- After that, press
**Enter**to get the**Rank**.

**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),"")`

- 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**

**How to Use AVERAGEIFS Function in Excel (4 Examples)****How to Use COUNT Function in Excel (With 5 Examples)****The Different Ways of Counting in Excel****How to calculate Average, Median, & Mode in Excel****How to Use CORREL Function in Excel (3 Examples and VBA)**

### 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**.

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`

- 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**.

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)`

- Next, press
**Enter**to get the**Rank**.

**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)`

- 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`

- Next, press
**Enter**to get the result.

**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**.

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 Error While Using RANK Function in Excel

#N/A error occurs with the **RANK** function 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

**How to use MAX function in Excel (6 Examples)****Use AVERAGE function in Excel (5 Examples)****How to Use SMALL Function in Excel (4 Common Examples)****How to Use MIN Function in Excel (5 Relevant Examples)****Use LARGE Function in Excel (6 Easy Examples)****How to Use COUNTIF Function in Excel (10 Suitable Applications)**