How to rank numbers in Excel

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.

how to rank numbers in excel

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.

using the rank.eq formula in excel

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.

using the rank.avg formula in excel

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!

Related articles

Super easy way to delete blank last page in Microsoft Word

Struggling with a blank page at the end of your Microsoft Word document? Discover a simple solution to eliminate that frustrating extra page and preserve your formatting!

Still the BEST camera to film walking videos in 2024? GoPro HERO 11 Black review

We do an in-depth review of the GoPro Hero 11 Black action camera to find out how it holds up for filming POV walking videos.

The complete guide to Microsoft PowerToys (2024)

With 22 utilities and counting, Microsoft PowerToys is an increasingly useful selection of tools to help improve how Windows works for you!

How to fix Dropbox Updater runtime error

Are you getting an annoying runtime error for Dropbox Updater when you fire up or shut down your computer? Read on, as we may have a fix for you!

Hey GoodGeeky, make me a travel itinerary! Using AI!

Say goodbye to stressful holiday planning and hello to personalized trip itineraries. Introducing the GoodGeeky AI-powered travel assistant!
Marius
Mariushttps://goodgeeky.com
I'm a writer, content creator and all-round creative. When I'm not writing for GoodGeeky, I film and edit YouTube videos, write books (which never seem to get finished), practice martial arts (while trying not to do my knees in or get kicked in the face), build websites and intranets, and work for The Man.