How to Use the RANK Function in Excel + Formula Examples
At first, this may sound like no big deal.😊
But think of a list of numbers that goes beyond hundreds of rows. Can you still rank these numbers without a smart automated tool?
If not, continue reading the article below to learn everything about the RANK function of Excel.
Download our free sample workbook here to tag along with the guide below.
Table of Contents
How to create rankings in Excel with the RANK function
How does the RANK function of Excel rank numbers? See below.
The above image shows the ages of different students.
Can we readily identify the eldest student and so on?
1. Write the RANK function as below.
= RANK (B2, $B$2:$B$6)
The first argument refers to the cell/value whose rank is to be found.
The second argument defines the range from where the relative rank of the value is to be found.
2. Hit ‘Enter’ to see the result.
Excel ranks Brian as ‘1’. He is 15 years of age and the eldest among all.
3. Drag and Drop the results to rank all students.
Did you notice that the range $B$2:$B$6 is an absolute reference instead of a relative reference? This is so that when we drag and drop the formula, the range remains the same and is not auto-changed by Excel.
Kasper Langmann, Microsoft Office Specialist5. Excel finds the ranks of all students.
Do you see something odd? Rank 3 appears twice, but there’s no 4.
That’s because two students (Daniel and Archie) have the same age.
The RANK function ranks duplicate values at the same position (Rank 3). And as two numbers take the same rank, the next rank is not 4 but 5.
Pro Tip!
The RANK function only works with numeric values. These can be numbers (positive, negative, and zeros), dates, and times.
If your data has any non-numeric value, it will be ignored by the RANK function.
Rank formula example #1: RANK and Dates
Here’s a list of events with their dates.
Tight Schedule, huh? Take a quick moment to sequentially rank the events.
1. Write the RANK function as below.
= RANK (A2, $A$2:$A$6, 1)
The first argument refers to the first date of the list whose rank is to be found.
The second argument defines the range of dates from where the relative position is to be found.
The third argument i.e. 1 refers to ascending order. When set to zero (or omitted), Excel sets it to the default value of 0. 😉
By default, Excel defines the ranks as largest to smallest.
2. Hit ‘Enter’ to see the result.
Excel ranks WHO Meeting as ‘1’. A glance at the list of dates tells it to be the first event.
3. Drag and Drop the results to rank all events.
This time the ranking is done in ascending order. The earliest date of all is ranked ‘1’ and so on.
Easy to Schedule meetings? Aren’t they? 😊
Excel considers each date a serial number. And so it ranks the dates just like numbers. Earlier date = Smaller number and vice versa.
Kasper Langmann, Microsoft Office SpecialistRank formula example #2: RANK and Time
You can also use the RANK function to rank times (highest to lowest or vice versa).
The image below shows a set of times.
Let’s rank them all to see the relative position of each time in the list.
1. Write the RANK function as below.
= RANK (A2, $A$2:$A$6)
We have created a reference for the first time in the list.
The remaining times (the whole list) are referenced as the range. Must note that this is an absolute reference.
2. Hit ‘Enter’ to see the result.
Drag and Drop the results to rank all times.
3. Excel ranks the latest time 7:45 PM as ‘1’ and so on.
4. If you want Excel to rank it otherwise i.e. earliest time first, add a third argument ‘1’ to the above formula.
= RANK (A2, $A$2:$A$6, 1)
Drag and Drop the same to the whole list.
See the change from the previous example? Excel ranks the earliest time 12:00 AM as ‘1’ and so on.
That’s how you can rank any numeric value in Excel and find its position relative to a list of numbers in no time.
Pro Tip!
The latest versions of Excel have two newer versions of the RANK function. RANK.EQ and RANK.AVG.
RANK. EQ Function in Excel: This stands for RANK EQUAL. Just like we saw above, when there are two numbers of the same rank, this function gives them the same rank.
RANK. AVG Function in Excel: This stands for RANK Average. When two numbers of the data set are at the same rank, it gives an average rank of them.
That’s it – Now what?
The RANK functions are not very intuitive. And so, it might take you time to understand how they work, the ascending and descending order, and the rules based on which numbers are ranked.
But it’s only about practice, and you’d feel pro enough to use it for your daily Excel jobs. The above article teaches the basics of the RANK function and offers different examples of how you may use it.
Want to get more productive with Excel? Master the three major functions offered by Excel: the VLOOKUP, SUMIF, and IF Functions.
My 30-minute free email course is designed to help you learn these and many more core functions of Excel. Sign up now!
Other resources
The RANK function is one of the most overshadowed functions of Excel. Make the best use of it by using it in combination with other relevant functions of Excel, like COUNTIFS to create a “RANK IF”.
Read our other blogs on how to add Analysis ToolPak in Excel. You might like to know about further ways of sorting and organizing data.