How to Rank a Set of Numbers in Excel

RANK function is used to compare numbers to other numbers in the same list. RANK function will tell you the rank of a given number from a range of number in ascending or descending order.

Syntax

=RANK(number or cell address, ref, (order))

This function is used at various places like schools for Grading, Salesman Performance reports, Product Reports etc.

Let’s understand it better with the help of an example.

We have a list of 10 students and their test scores from cell B2 to B11 as shown below:

To find the rank of the first student’s score in cell B2, enter this formula in cell C2:

=RANK(B2,$B$2:$B$11)

Just double click the fill handle and it will copy the formula from cell C2 down to cell C11. The scores will be ranked in descending order as shown below:

Arguments of RANK Function

There are 3 arguments for the RANK function:

1. Number or cell address: In our example above the number to be ranked is in cell B2.
2. Ref (range): We want to compare the number to the list of numbers in cells from B2 to B11.An absolute reference will be used ($B$2:$B11) instead of a relative reference (B2:B11), so that the referenced range doesn’t change when we copy the formula to the cells below.
3. Order: This argument is optional. This argument will tell Excel to rank the list in ascending or descending order as required.

Ranking in ascending or descending order

Using 0 (zero) or leaving this argument empty will give the ranks in descending order.

In our example, we left the order argument blank in order to find the rank in descending order.

The formula used is =RANK(B2,$B$2:$B$11)

For ranking them in ascending order, we need to type 1, or any other number except zero.

For comparing golf scores, we could type 1, to rank in ascending order.

=RANK(B2,$B$2:$B$11,1)

A question might arise here that what will happen to the ranking if some of the scores are similar in other words scores tie with other scores?

Microsoft Excel will automatically skip subsequent numbers to show the correct rank.

As we can see in our example, the last two scores in the list are the same. Both the students Ivy and Joe are ranked as 4^th.

The score for Ed is 36. Excel has ranked it 6^th instead of 5^th because there are 5 students ahead of Ed, two of them having 4^th rank.

If you have any queries please post them in comments. I will be more than happy to help.