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. Before, I’ll show the basics of the function.

**RANK Function in Excel (Quick View)**

**Table of Contents**hide

## Download Excel Workbook

## Excel RANK Function: Syntax & Arguments

Firstly, you’ll see the syntax and argument of the function. If you insert the function after entering **equal sign (=)**, you’ll see the following figure.

### Summary

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

### Syntax

`=RANK (number, ref, [order])`

### Return Value

Rank number

### Arguments

Argument |
Required or Optional |
Value |

number |
required | The number that you want to rank |

ref |
required | The reference (an array or a list of numbers) containing the number |

order |
optional | The way of ranking (descending or ascending order) |

**Note.**

- Microsoft warns that the
**RANK**function may not be available in the future as they developed more new and better functions for ranking with better accuracy and usage. - In the case of
argument, 0 is used for descending order and 1 is used for ascending order. If you omit the order (as it is an optional argument) while inserting the**order****RANK**function, the function will sort automatically in descending order.

## How to Use the RANK Function in Excel (With 5 Examples)

### Example 1: Using RANK Function in Descending Order

Let’s imagine, we have a dataset where exam scores are given to students. Now, we will find the rank based on the scores. Firstly, we will learn how to rank in descending order.

Just insert the following formula.

`=RANK(C5,$C$5:$C$15,0) `

Here, C5 is a score, C5:C15 is the list of scores, 0 is for the descending order.

Note. You must use the **dollar sign ($) **inside the list of scores to get an accurate ranking.

### Example 2: Using RANK Function in Ascending Order

Again, we will find the rank for the exam scores in ascending order. Here, the formula will be the same except 1 will be used for the ascending order.

`=RANK(C5,$C$5:$C$15,1)`

### Example 3: Using RANK Function in Non-Contiguous Cells

Sometimes you have to face a situation where to rank blank cells or non-contiguous cells.

In that case, you may have to use the following formula.

`=IFERROR(RANK(C5,($C$5,$C$6,$C$9:$C$12),0),"")`

Here, the **IFERROR** function is used to avoid non-ranked cells.

### Example 4: Getting Unique Rank Using 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 scores.

How can we solve the problem?

`=RANK(C5,$C$5:$C$15,0)+COUNTIF($C$5:C5,C5)-1`

Here, the syntax **COUNTIF($C$5:C5,C5)** counts 2 for the second score (C7 in the dataset) of the two same scores. But the syntax counts 1 for all scores.

Later, after subtracting 1, we can easily get a unique rank.

### Example 5: Using RANK Function to Break Ties

In certain cases, you cannot apply the previous method to get a unique rank.

Assuming that the number of time (in minutes) that students take to finish the exam are given.

If any student takes less time in an exam to finish, he or she will be ahead of the other who has the same score but takes more time.

Now, insert the following formula.

`=IF(COUNTIF($C$5:$C$15,C5)>1,RANK(D5,$D$5:$D$15,1)/100,0)`

Here, the **COUNTIF($C$5:$C$15,C5)>1** counts the ties.

Then the **RANK(D5,$D$5:$D$15,1)** sorts the time in ascending order (as less time is the criteria for a better rank) for the ties.

Later, we have to divide by 100 to get the output in decimal value.

After entering the formula, you’ll get the following output.

Lastly, we have to determine the final rank. For this, just sum the tied rank and tie break.

`=E5+F5`

Now, see the following figure that shows James is ahead of Michael as James takes less time in the exam to finish.

## Common Errors While Using the RANK Function

Common Errors |
When they show |

#N/A |
Occurs when the given number that you want to find rank is not available in the reference (the list of numbers). |

## Conclusion

This is how you can apply the **RANK** function to get the row number. 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.

## Further Readings

- How to use MAX function in Excel (6 Examples)
- How to 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)
- How to Use LARGE Function in Excel (6 Easy Examples)
- How to Use COUNTIF Function in Excel (10 Suitable Applications)
- 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