There’s been so many times in the past where I’ve needed to rank numbers in Excel from one to whatever based on size, weight, height, score, duration or any other number of factors.
I must have been living under a rock, because it was only yesterday that I realised Excel can rank stuff for you with two really simple formulae: =RANK.EQ() and =RANK.AVG().
I don’t know why I never thought to look into it… It randomly came up in an email newsletter called the Morning Brew, which I subscribe to. That newsletter has nothing to do with Excel, but there you go.
Anyways, here’s a tech tutorial showing you how to rank stuff in Excel!
First you’ll need a list of things to rank. We’re going to rank cars based on how fast they accelerate from 0 to 100 kilometres per hour (that’s 0 to 62 miles per hour if you’re metrically challenged). Of course, you can rank anything based on a list of numbers, but let’s stick with cars.
Here’s a list of cars. And how fast they accelerate. If you’re both an Excel nerd and a rev head, go easy on me. I grabbed these numbers off the first website I could find, so the numbers aren’t necessarily accurate.
Anyways, I will show you two rank functions in Excel. The first one is rank equal. The other one is rank average.
How to rank numbers in Excel using the RANK EQUAL formula
If you’re into sports, you’ll like rank equal. You know how if you have two runners finish their 100 metre sprint in exactly the same time, they’ll share the rank – so equal first, for example… That’s what the rank equal function does.
To do rank equal, just type =RANK.EQ. You can hit tab once the formula you want is highlighted to autocomplete it.
Then, the first thing you want to select is the number next to the car on this line.
Then hit comma, and select the entire list of numbers. Remember to make this an absolute reference by hitting F4. Otherwise, when you copy the formula down, it’ll get messed up.
Hit comma again, then you have to select 0 for descending or 1 for ascending. What that means is, if you select to rank the items in descending order, it’ll make the largest number rank as number one.
But, for our cars, low is better, so we’ll select 1 for ascending, meaning the car with the fastest acceleration will be ranked number one.
Once you’ve done that, put in the ending bracket and hit enter.
Then you can copy the formula down to the other rows or just double click the little dot in the bottom-right corner of the cell and Excel will take care of it for you.
So there you go! The Saleen S7 is pretty awesome when it comes to accelerating quickly.
Notice how the Ferrari, Lamborghini and McLaren all do 0 to 100 kilometres per hour in 2.9 seconds. That’s the fourth fastest time, so Excel has ranked them all as equal fourth, and then the next one down is number seven.
How to rank numbers in Excel using the RANK AVERAGE formula
Let’s see what the rank average formula does! It works the same. Just type =RANK.AVG, hit tab to autocomplete, and select the same stuff again.
This time around, you’ll see Excel has ranked the three cars that do 0 to 100 in 2.9 seconds as equal fifth.
That’s because these three cars would occupy the fourth, fifth and sixth place on the ranking, so it’s gone ahead and averaged that. And as you know, four plus five plus six equals 15, then divide that by three to get five.
But what if there were only two cars sharing the same rank? Let’s fudge the numbers! Maybe the Lambo had a bad day and actually only managed 3.1 seconds?
The two cars in our list that do 2.9 seconds now share fourth and fifth place. They’re still showing as equal fourth in the rank equal column. But in the rank average column, they’re now showing as 4.5. Because four plus five equals nine, divide that by two and you get 4.5.
So there’s how to rank numbers in Excel! Was that useful? Let us know in the comments. And don’t forget to check out more of our Microsoft 365 tutorials!
