course

# Rank Formula in Excel: A Comprehensive Guide With Examples

Ranking lets you compare values and identify patterns within your datasets. Excel provides powerful tools such as `RANK()`

, `RANK.EQ()`

, and `RANK.AVG()`

to make ranking easier. These functions are helpful in all situations, from ranking participants in a sports tournament by points to analyzing market trends based on ranked sales data.

In this article, we’ll explore all three rank functions in Excel and explain how you can use them with hands-on examples. We will also dive into using rank functions with other Excel functions and calculating percentile ranks.

## The Quick Answer: How to Calculate Rank in Excel

To calculate the ranks in your desired datasets, use the following formula:

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

## Understanding the RANK() Function in Excel

Let’s say you organized a competition among school students and now want to rank thousands of students based on their performance.

Would you spend hours sorting through data? No, because Excel can help. Among its many powerful functions is the `RANK()`

function, which lets you determine someone's position with a single click.

To calculate the rank of a value within a range of values, enter the formula:

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

In the above formula:

`number`

: is the number you want to rank.`ref`

: is the range of numbers to take into account for ranking.`order`

: specifies the rank order. For ascending, use “1” as a value; for descending, use “0” (default).

## How to Use the RANK() Formula in Excel

Let’s implement the `RANK()`

function on real-world examples:

### Ranking students grades

Calculating grades would be challenging and time-consuming if you have thousands of students. Suppose you're preparing final results for students and want to rank them based on scores.

To do so, first, prepare your dataset.

Students example dataset in Excel. Source: Image by author.

Enter the formula `=RANK(B3,($B$3:$B$7))`

in one cell and drag it to the last filled cell to get the rank.

Using the RANK() function to calculate the rank of students. Source: Image by author.

You now have the student’s rank in just one click.

But did you notice that two students with the same marks have the same rank of 4? The following student, with 23 points, jumps to rank 6, skipping rank 5. This means that the formula assigns the same rank to duplicate scores.

This can mess up the order for close scores. But you can fix these ties using the `COUNTIF()`

function. Enter the following formula:

`=RANK(B3,($B$3:$B$8)) + COUNTIF($B$3:B3, B3)-1`

The `COUNTIF()`

function adjusts the rank for duplicate values. It counts how many times the current student's marks appear above them in the list.

Breaking ties using the COUNTIF() function in Excel. Source: Image by author.

A value of 28 appears twice, and `COUNTIF()`

tracks the occurrence count. This occurrence count is used to adjust the rank. The formula adds the count of occurrences minus one to the original rank. This ensures that each duplicate value receives a unique rank.

### Ranking sales figures in a dataset

Let’s look at another example to show how to use the Excel `RANK()`

function in ascending or descending order.

If you want to assess your sales team's performance, you can rank their monthly sales figures using Excel's `RANK()`

function.

Here, we have a dataset with monthly sales figures for each salesperson. We'll use the `RANK()`

function to rank them based on who brought in the most sales (highest to lowest).

List of sales figures to rank. Source: Image by author.

To rank the sales in ascending order, we applied the formula `=RANK(B2, ($B$2:$B$6), 1)`

. Here, a value of “1” for the `order`

parameter specifies the ascending order for the ranking.

Ranking the values in ascending order. Source: Image by author.

Once you've entered the formula, simply hold the cursor, drag the corner of the cell down, and apply it to the rest of your sales data.

Similarly, if you want to rank the values in descending order, write “0” as the `order`

parameter value in the formula like this: `=RANK(B2, ($B$2:$B$6), 0)`

You can see the difference in the results:

Ranking the values in descending order. Source: Image by author.

You may notice the dollar signs in the references `($B$2:$B$6)`

. These are called absolute references. They ensure the formula always refers to the same sales data range, even when you copy it down, to prevent errors.

If you don’t use the absolute reference, like `=RANK(A2, A2:A10)`

, the range will adjust as you drag down:

- Formula in G3:
`=RANK(G3,(G3:G7), 1)`

- Formula in G4:
`=RANK(G4,(G4:G8), 1)`

To prevent this issue, use absolute references by pressing “F4”. Now the formula will be: `=RANK(G3,($G$3:$G$7), 1)`

When you drag the above formula to the last filled cell, relative references will change based on each cell's position. You can see the difference in the results when using absolute references:

Difference between the ranks with and without absolute reference. Source: Image by author.

## Advanced Rank Excel Functions: RANK.EQ() and RANK.AVG()

When you encounter duplicate values in Excel, things can get tricky. That's where I prefer using the `RANK.EQ()`

and `RANK.AVG()`

functions. Both of them are slightly different to handle. Let’s look at how they work.

### The RANK.EQ() Excel function

`RANK.EQ()`

works just like the regular `RANK()`

function but with a more precise name. When there are duplicates, `RANK.EQ()`

assigns the same rank to all of them and simply skips the next rank(s) in line.

Syntax: `=RANK.EQ(number, ref, [order])`

In the above formula:

`number`

: is the number you want to rank.`ref`

: is the range of numbers to take into account for ranking.`order`

: specifies the rank order. For ascending, use “1” as a value; for descending, use “0” (default).

Let’s understand this formula with an example: I had a dataset with the populations of several countries, and I wanted to rank these countries based on their populations.

Here's how I did it using the `RANK.EQ()`

function:

Using the RANK.EQ() function. Source: Image by author.

In the image above, the country with the highest population is ranked 1, and so on. Now, you see how `RANK.EQ()`

handles ties.

Since the US and Indonesia have identical populations, they share the same rank of 2. This makes it clear how `RANK.EQ()`

assigns the same rank to duplicates and skips the next rank in the sequence.

### The RANK.AVG() Excel function

The `RANK.AVG()`

function takes a different approach to handling ties in rankings. Instead of assigning the same rank to all duplicates, it calculates the average of the ranks they would have received individually.

Syntax: `RANK.AVG(number, ref, [order])`

In the above formula:

`number`

: is the number you want to rank.`ref`

: is the range of numbers to take into account for ranking.`order`

: specifies the rank order. For ascending, use “1” as a value; for descending, use “0” (default).

Let’s understand this with an example: I had a dataset of scores, so I applied the `RANK.AVG()`

function to see how it handles tie scores.

Using the RANK.AVG() function. Source: Image by author.

Using `=RANK.AVG(D3,$D$3:$D$6)`

, the score 88, receives a rank of 2.5. This means the function averages the ranks it would have assigned individually.

This approach maintains a more balanced distribution of ranks, especially in datasets with frequent ties.

## Combining RANK() With Other Excel Functions

Using the `RANK()`

function with other Excel functions is helpful in some scenarios. Let’s review a couple of common use cases.

### Combining RANK() and ROUNDUP() to categorize data

Here’s how you can combine the `ROUNDUP()`

function with the `RANK()`

function:

Combining the RANK() and ROUNDUP() functions in Excel. Source. Image by author.

I used the formula `=ROUNDUP(RANK(B2, $B$2:$B$7, 1)/3, 0)`

, which calculates the rank of the value in cell B2 within the range B2 in ascending order. It then divides this rank by three and rounds up the result to the nearest integer.

With the above formula, you effectively categorize the data into distinct groups or tiers, which can help identify and analyze different performance levels or categorize data for further analysis.

### Combining RANK.EQ() and COUNTIFS() to break ties

We have discussed a few ways to handle ties using Excel's `RANK()`

function. However, let’s say you want to rank products solely based on price. This might feel arbitrary when multiple items share the same cost.

So, let's introduce a secondary criterion to break ties and create a more objective ranking system.

Consider a list of customers who have bought headphones. We want to rank them by their price (primary criteria) and then use customer rating (secondary criteria) to differentiate in case of a tie.

Example list of customers. Source: Image by author

To achieve this, we'll combine the `RANK.EQ()`

function with the `COUNTIFS()`

function. Here’s how:

`=RANK.EQ($B2,$B$2:$B$9) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, ">"& $C2)`

Calculating rank using RANK.EQ() and COUNTIFS(). Source: Image by author.

The section `COUNTIFS($B$2:$B$9,$B2)`

counts how often the current price appears in the list. We use absolute references for the range, `$B$2:$B$9`

, to maintain consistency but keep the criteria cell, `$B2`

, relative so the formula checks each row individually.

The second part, `$C$2:$C$9,">"&$C2`

, finds the number of customer ratings higher than the current headphone's rating, C2.

`COUNTIFS()`

follows “AND” logic: It only counts cells that meet all conditions. If no other headphones at the same price have a higher rating, `COUNTIFS()`

returns 0. That’s why the rank determined by `RANK.EQ()`

remains unchanged.

## Calculating Percentile Rank in Excel

Since the `RANK()`

function ranks a numeric value, you can use an Excel percentile rank formula to calculate the value rank within a dataset as a percentage of the total dataset.

Excel provides two functions for calculating percentile ranks. Let’s explore both of them.

### The PERCENTRANK.INC() Excel function

PERCENTRANK.INC() calculates a value's percentile rank, including the smallest and largest values in the dataset.

Let’s understand this function with an example: I had a dataset representing the exam scores of 10 students, and I wanted to calculate the percentile rank of each score, including the smallest and largest values.

So, I used the formula `=PERCENTRANK.INC($A$2:$A$11, A2)`

:

Calculating the rank using the PERCENTRANK.INC() function. Source: Image by author

In the above example, you can see the `PERCENTRANK.INC()`

function includes the smallest and largest values in the dataset. The score 45 (smallest value) has a percentile rank of 0, and the 90 (largest value) score has a percentile rank of 1.

### The PERCENTRANK.EXC() Excel function

`PERCENTRANK.EXC()`

calculates a value's percentile rank, excluding the smallest and largest values in the dataset.

Let’s understand this function with an example: I applied the `PERCENTRANK.EXC()`

function on the same student dataset from the previous section:

Calculating the rank using the PERCENTRANK.EXC() function. Source: Image by author

In the above example, the `PERCENTRANK.EXC()`

function excludes the smallest and largest values in the dataset. This means the score 45 (smallest value) does not have a percentile rank of 0, and the score 90 (largest value) does not have a percentile rank 1.

## Excel RANK() Function Common Mistakes and How to Avoid Them

Excel functions are handy and easy to use for all business work. But sometimes, even the most skilled users can make a few common mistakes. So, I’m sharing some of the most common mistakes to look for and how you can avoid them:

### Copy-pasting formulas

When you copy your ranking formula across cells, make sure you don't accidentally use absolute references. Each cell can reference a different chunk of data and disrupt your rankings.

To avoid this, use absolute references, like `$A$1:$A$10`

, when setting up your formulas to lock in the correct range across all your rankings.

### Not handling duplicates

Let's say you have two salespeople tied for the most sales. How you handle ties in your ranking can make a big difference: the `RANK()`

and `RANK.EQ()`

functions treat ties similarly.

So, you should decide if tied values deserve the same or different rank or if their ranks should be averaged using `RANK.AVG()`

. Then, use the correct function accordingly.

### Wrong ordering

It seems simple, but forgetting to set the `order`

argument in your function can sort rankings in the wrong direction (ascending or descending).

That’s why you should always double-check this argument: Use “0” (or leave it blank) for descending order and “1” for ascending order. Make sure the order aligns with how you want your data ranked.

### Function fumble

When you work with different datasets, it’s so important to choose the correct rank function because proceeding with the wrong ranking function will give undesirable results.

To avoid these issues, use `RANK.EQ()`

for standard ranking and `RANK.AVG()`

for scenarios where tie handling affects the outcome, like statistical analysis.

### Using outdated formulas

As your data grows, you must update the range references in your formulas. This means new data points won't be included in your rankings, and you may get frustrated about why you are not getting your answer right.

That’s why you should regularly update and check the ranges in your formulas. This ensures that all your valuable data is considered for ranking.

## Final Thoughts

Now you know how to use functions like `RANK()`

, `RANK.EQ()`

, and `RANK.AVG()`

to handle ties and solve various ranking problems. These functions help in different scenarios, such as analyzing student grades or ordering sales figures.

If you’re interested in expanding your Excel skills further, I highly recommend checking out some excellent courses and resources available on Datacamp:

- Start with the Introduction to Excel course to build foundational knowledge, then advance to Data Analysis in Excel and Excel Fundamentals.
- Consider the Data Visualization in Excel course to enhance your ability to visualize data.
- The Introduction to PowerQuery in Excel course greatly benefits more advanced data manipulation.
- However, to formalize your Excel expertise, check out this comprehensive guide on How to Earn a Microsoft Excel Certification.

## FAQs

### What is the difference between RANK.EQ() and RANK.AVG()?

`RANK.EQ()`

assigns the same rank to duplicate values, while `RANK.AVG()`

assigns the average rank to duplicate values.

### Can you rank text values with the RANK() formula?

**No, the RANK() formula is designed for numeric values. You would need to use different functions like RANK() combined with other logic for text values.**

### What is the purpose of the COUNTIF() function in adjusting ranks for ties?

**The COUNTIF() function in Excel uses the RANK() formula to count the occurrences of the value up to the current cell and adds this count (minus one) to the original rank. **

### What is the error in the RANK() formula in Excel?

Two different errors can occur while using the `RANK()`

formula: `#VALUE!`

Error and `#N/A`

` `

Error.

`#VALUE!`

`RANK()`

function contain non-numeric values. Ensure that all cells in the range being ranked and those whose rank is calculated contain numeric data.`#N/A`

Error: This error occurs if the ranked cell is outside the specified range. To avoid this error, verify that the value ranked is included in the selected range.

I'm a content strategist who loves simplifying complex topics. I’ve helped companies like Splunk, Hackernoon, and Tiiny Host create engaging and informative content for their audiences.

Learn more about Excel and data analysis with these courses!

course

### Data Preparation in Excel

course

### Data Visualization in Excel

blog

### Working with Pivot Tables in Excel

cheat sheet

### Excel Formulas Cheat Sheet

tutorial

### Conditional Formatting in Excel: A Beginner’s Guide

tutorial

### Linear Regression in Excel: A Comprehensive Guide For Beginners

tutorial

### Pivot Tables in Spreadsheets

tutorial