Using Excel's RANK function to order numbers

In this article, we explain how to use Excel's RANK function to determine the largest 3 numbers in a list. Imagine that we have the data below and we want to find the top 3 ranked values:-

 Note for this example, we  assume that there are no two people having exactly the same score.

The function RANK allows us to do this. It requires 2 arguments – the cell that we are comparing and the range over which we are comparing :-

RANK(A7, A2:A20)

This gives us the ranking of Cell A7 in the range A2:A20. So if we apply to this to our formulae and copy down :-

We see that the highest marks are Anthony, Mark and then Jordan – having 35, 30 and 28 marks respectively.

Note so far we have only determined the rankig of the names – not the actual names of the people. To do that we need to find the position of the ranking – i.e the fact that the 2nd largest value is on the 4th row of our data, and that the 4th largest is on the 6th row. This is done by doing the MATCH function :-

MATCH(A2, A2:A10,0)

Will give us the position of contents of the cell A2 In the range A2:A10. So we can add another column to determine the position of the nth largest item :-

And then we can set space on the workbook to display the three largest values :-

And we state the position of the 3 largest items – using the MATCH function that we have just defined :-

This will return the position in the list of the largest 3 items. We can then use in the argument of an INDEX function to return the name of the students with the 3 highest marks :-