How To Use The RANK Function In Excel.
Rank Data In An Instant!

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Like the INDEX and MATCH functions, RANK gives you information on where a particular value falls in a list. And at first, it might not seem like a very useful function.

But once you get the hang of it, you can start using RANK to get some great information about your spreadsheet.

Let’s take a look at how to do that.

Why there are two RANK functions

In recent versions of Excel, RANK has actually been split into two different functions: RANK.AVG and RANK.EQ.

They provide similar results, but how they differ is important.

RANK.AVG returns the average position of multiple results. So if your reference list goes 4, 3, 3, 2, 1, the average rank of 3 would be 2.5.

RANK.EQ returns the highest rank of multiple results. In the reference list 4, 3, 3, 2, 1, this function returns 2, because that’s the highest rank of the number 3.

You can still use the RANK function without appending AVG or EQ, but Microsoft recommends that you use one of the newer functions. RANK might not work in future versions of Excel. But while it does work, it functions like RANK.EQ.

rank-functions

Keep in mind that Excel treats your reference list as if it were reordered.

So the above example could have used a list of 2, 3, 1, 3, 4—and the results would have been the same.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Getting to know the RANK functions is easiest when you have a spreadsheet to work on.

We’ve created this free example workbook to help you out:

Download the FREE Exercise File

Download exercise file

How to use the RANK.AVG function

We’ll start with the RANK.AVG function. Let’s take a look at the syntax first.

The syntax of the RANK.AVG function

=RANK.AVG(number, ref, [order])

As we saw above, number is the number you’re looking for in a list of numbers.

ref tells Excel where to find the list that it should be looking in.

The final argument, order is optional. If you enter 0 or omit the argument altogether, Excel ranks the number as if ref were a descending ordered list.

Entering a 1 makes Excel rank number as if ref were an ascending ordered list.

Let’s take a look at these arguments in more detail.

If you’re confused by the “as if ref were a descending/ascending ordered list” part, don’t worry. It’ll become clear in the example below.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s take a look at our example workbook to see how RANK.AVG works.

On the first sheet, there’s a series of ID numbers, last names, and results. Some of the ID numbers and names are repeated, as are some of the results (though not always with the same numbers and names). The list is ordered by ID number.

We’ll find the average rank of a particular result with the following formula:

=RANK.AVG(10, C2:C51)

rank-avg-formula

Because we’re looking for the number 10, that’s our first argument. The list in which we’re looking is in the C column, so that’s our ref value. And because we want the numbers in the ref list in descending order, we can leave the order parameter blank.

This means that the highest number is the highest rank. In our case, the highest score is 49—so a score of 49 will be rank #1.

If we had chosen a descending order, the lowest number would be rank #1.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s hit Enter and see what happens.

rank-avg-10

The formula gives us an average rank of 41.

It’s always a good idea to see if your results are reasonable, so let’s check it.

Reordering the Score column in descending order, we see that there are 10s in positions 40, 41, and 42, creating our average rank of 41.

RANK.AVG can also return decimal results if the average isn’t an integer.

Calculate the average rank of a score of 2, and you’ll see that the result is 47.5:

average-rank-2

Looking at our sorted Score column, you can see that there are 2s in positions 47 and 48.

When should I use the RANK.AVG function?

It might not be clear when you’d need to use this function. But the example above should help you think of a few cases.

When you have multiple results for multiple people, you can see the average rank of a person’s results. This could be useful with test scores, job performance, and anything that’s measured more than once and compared to other measurements.

How to use the RANK.EQ function

As mentioned previously, RANK.EQ is the same as the deprecated RANK function.

It has one crucial difference from RANK.AVG: if the ranked number is present more than once, it only returns the highest rank.

The syntax of the RANK.EQ function

=RANK.AVG(number, ref, [order])

As we saw above, number is the number you’re looking for in a list of numbers.

ref tells Excel where to find the list that it should be looking in.

The final argument, order is optional. If you enter 0 or omit the argument altogether, Excel ranks the number as if ref were a descending ordered list.

Entering a 1 makes Excel rank number as if ref were an ascending ordered list.

The second sheet of our example workbook contains data that will help you see how RANK.EQ works.

There’s a crucial difference between the second and first sheets. Where the first sheet contains scores, of which the highest is the best, the second sheet contains places, where the lowest is the best (first place is better than second).

So in this example, we’ll be using the ascending order.

Let’s find out the highest rank that the number 3 occupies. Here’s the formula we’ll use:

=RANK.EQ(3, C2:C51, 1)

rank-average-example

The first two arguments are like those in the previous example, but this time we’ve included a 1 in the order parameter, telling Excel that our list is in an ascending order.

Hit Enter, and you’ll get the result:

As you can see, Excel returns a rank of 11.

This brings up an important point.

Duplicate numbers are all given the same rank—so all six of the 1s in this spreadsheet are given rank 1. But they affect the rankings of later numbers.

So the first 2 in the spreadsheet has a rank of 7. So do all the other 2s.

This is why the highest rank occupied by a 3 is 11, and not 3.

When should I use the RANK.EQ function?

So when should you use RANK.EQ instead of RANK.AVG?

When you only want to know the highest rank that a particular number has occupied. In the above example, we found that a place of 3, which seems very good, is only the 11th-best performance in the sheet.

This might seem like a small difference from RANK.AVG, but when you’re looking for the highest rank a particular number occupies, this function is what you need.

A note on reference lists

So far in this tutorial, we’ve used columns as reference lists. Most people are likely to need to rank numbers in single columns.

However, you can also use arrays for reference lists.

Take a look at the third sheet in the example workbook for a simple example.

Here we have an array with three columns and three rows.

Let’s try ranking the number 7 in this array.

As you can see, 7 is given a rank of 7 in ascending order, and 3 in descending order.

From this, we can infer that Excel goes down the first column, then down the second column, then down the third column. Keep that in mind when you’re ranking a number in an array.

Start ranking like a pro

RANK isn’t an intuitive function at first, but with a bit of practice, it becomes much easier to use.

Getting used to the ascending and descending options, as well as understanding how numbers are ranked in arrays, will help you start using the functions in your day-to-day work.