How to Calculate a Rank in Excel (Descending / Ascending)

How to Calculate Rank in Excel

In this tutorial, we’re going to have a look at how to calculate a rank in Excel. Based on the calculation, you’ll be able to compare data of any size and determine their position within the data group.

Ready to dive in?

Would you rather watch this tutorial? Click the play button below!

How to Calculate a Rank in Excel

To rank data, first we need to click into the cell where we want to display the result.

We start typing the equal sign and the first letters of the word ‘RANK’, and Excel will come up with the right function suggestion immediately.

How to Calculate a Rank in Excel - Rank function

Here we need to note that the Rank function is available for compatibility with Excel 2007 and earlier.

How to Calculate a Rank in Excel - Rank function only available for compatibility with Excel 2007 and earlier

Let’s click on the suggested function and fill in the missing bits. First, we need to enter the number we want to calculate the rank for. This value is stored in the cell C3, which contains the information on Tommy’s sales.

How to Calculate a Rank in Excel - enter the number we want to calculate the rank for

We can add a comma and now we need to include all the numbers of the data set which we want to compare and evaluate, and against which we want to determine Tommy’s position within the ranking. So, here we’ll select the sales by the rest of the salesmen.

How to Calculate a Rank in Excel - numbers of the data set which we want to compare and evaluate

However, we need to be careful not to forget to lock the reference to this set of data. Click on C3 and press and hold the Function key along with F4. Some of you don’t have to use the Function key – this depends on the keyboard type you’re using.

How to Calculate a Rank in Excel - hold the Function key along with F4

The dollar sign appeared next to the cell reference, which means that the value for calculating the rank will stay the same once the function is copied in the rows below.

How to Calculate a Rank in Excel -The dollar sign appeared next to the cell reference

We use the same keys to fix the coordinates C7 to make sure that the reference to the whole area will remain unchanged.

How to Calculate a Rank in Excel -We use the same keys to fix the coordinates C7

If we copy the Rank function to the rest of the rows below, the reference to the relevant cells won’t change, which is important as we want to find out the rank for each of the salesmen, so we need to keep the set of data the same in each calculation. If you’re interested to know more on how to use the absolute cell reference in Excel, check out a separate tutorial the link to which is in the list below.

How to Specify the Rank to Be Descending or Ascending

But let’s move on now and add another comma. Now it’s time to specify whether we want the rank to be descending or ascending.

How to Calculate a Rank in Excel - the rank to be descending or ascending

If we go for descending, the highest number on the list will become number one and the lowest sales count will be the last in the rank. This option is set as default in Excel, so if we close the bracket at this point, without adding any extra information, Excel will go on and calculate a descending rank.

How to Calculate a Rank in Excel - descending rank

To calculate the ascending rank, we need to choose the option ‘ascending’. In that case, the lowest number will become number one and the highest number will be marked as the last in the ranking.

How to Calculate a Rank in Excel - ascending rank

Let’s go for ‘descending’ now – click on it, close the brackets, hit Enter and here we are! You can see the position of Tommy’s sales compared to the sales by the rest of the salesmen.

How to Calculate a Rank in Excel - descending order
How to Calculate a Rank in Excel - the position of Tommy’s sales compared to the sales by the rest of the salesmen

Copy the function simply by dragging the bottom right-hand corner of the cell containing the complete formula and the ranking for other salesmen appears right away.

How to Calculate a Rank in Excel - copy the function

As you can see, the first one in the ranking is the salesman who got the highest number of sales and the last one is the one with the lowest sales count, just as we wanted.

How to Calculate a Rank in Excel - the first one in the ranking is the salesman who got the highest number of sales
How to Calculate a Rank in Excel -  the last one is the one with the lowest sales count

How to Calculate a Rank in Excel If There Are Two Values which Are the Same

There’s one important thing to be aware of – if there are two values which are the same within the selected data area, Excel will do the evaluation in a specific way.

Here you can see that the third position belongs to two salesmen with the same sales counts. They’ve been both put in the same rank, which is three, but since there are two people with the same numbers, Excel skipped position four and the ranking continues with position five.

How to Calculate a Rank in Excel -  the third position belongs to two salesmen with the same sales counts
How to Calculate a Rank in Excel -  Excel skipped position four and the ranking continues with position five

If there were three identical values on the same position, the following two places would get skipped, so make sure to keep this rule in mind when calculating a rank in Excel.

Don’t miss out a great opportunity to learn:

If you found this tutorial helpful, give us a like and watch other tutorials by EasyClick Academy. Learn how to use Excel in a quick and easy way!

Is this your first time on EasyClick? We’ll be more than happy to welcome you in our online community. Hit that Subscribe button and join the EasyClickers!

Thanks for watching and I’ll see you in the next tutorial!

Comments

Add comment

Your email address will not be published.

Your personal information will only be used for the purpose of processing this comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed.