How to Rank Data in Excel

The Excel RANK.EQ function (formerly the RANK function, which technically still works) is a very handy function to rank numbers within a dataset. It’s an easy way to quickly rank things (and using the formula will keep the ranking up-to-date) and can be used in a variety of ways: ranking of students scores, ranking players or times in sports, ranking sales metrics, ranking NCAA Tournament brackets, and more. Here’s how to do it.

First off, get your spreadsheet together with the data you want to rank. In our example, we’re using test scores:

Now we want that “Rank” column to have that student’s rank in the class. This is easy to do with the RANK function. The syntax is basically this:

=RANK.EQ(number,ref,[order])
  • number – The number to rank.
  • ref – A range that contains numbers to rank against.
  • order– [optional] Whether to rank in ascending or descending order (by default is descending).

So in our case, our for ours, we want our formula to look like this:

=RANK.EQ(C5,$C$5:$C$14)

Note we’re using absolute references for the range because we don’t want the column reference to change when we copy the formula down into the rest of the cells.

After we put that formula in the first cell in the “Rank” column and then drag it down into the rest of the cells, we end up with this:
If you look closely at the results, you’ll see that there are two “3” ranks and no “4” rank. That’s intentional. The scores for Terry and Ely are the same (92) so they both are tied at 3. That would put the next person (Lawson) at 5 (as there are four scores higher than Lawson’s). If you want to treat ties a bit differently and average them instead, you can use the RANK.AVG function which will average out the rank of ties. So in the above example, the two folks tied at 3 would be ranked at 3.5.

If you’re looking for the fastest time, you can tweak the formula to sort ascending and rank them accordingly:

=RANK.EQ(C5,$C$5:$C$14,1)

Then you’ll end up with something like this:
This is a very simplistic example, obviously, but you get the idea.

For more examples and use cases, you can read more on the various RANK functions at ExcelJet.

Comment : 0
LEAVE A REPLY

Leave a Reply

Your email address will not be published.

Weston

- A Team That Supports Your
People, Not Just Your Technology