How to use the rank formula in excel?

The rank formula in excel is a very useful and handy tool to get a rank either in descending or ascending order.

What is the Rank Formula in Excel?

The RANK function is categorized as an Excel Statistical Function. The function returns the statistical rank of a given value within a supplied array of values. It is used to determine the position of a specific value in an array.

The Syntax

  • Rank formula in excel syntax

          =RANK( number, ref, [order])

  • Parameters
  1. Number – A number for which the rank is to be found
  2. Ref – This can be a list, or an array, or a reference
  3. Order – The order in which the numbers should be ranked. This is an optional parameter
  • Output of rank formula

          The RANK function returns a numeric value.

  • Note
  1. If an order is 0, the numbers are ranked in descending order.
  2. If an order is not 0, the numbers are ranked in ascending order.
  3. If the order parameter is omitted, it assumes the order is 0
  • Applies to:

          Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007.

Example 1 :for using the rank formula in excel

Assume we are given a list of employees with their respective medical reimbursement expenses. We wish to rank them according to total expenditure.

rank formula in excel

The result we get is shown below:

Syntax for rank formula in microsoft excel

Types of Rank formulas in excel

There are various kinds of rank formulas in excel, so let me quickly take you through them.

Rank.EQ Formula in Excel

Now, there are so many people who get confused with the explanation written on this function. Also, especially the explanation given by microsft itself.

However, let me clarify that, the Rank.EQ is nothing but the rank function itself. Also, the plain and simple rank function in excel is shown with a yellow exclamation mark. Which means, that it’s the older version and rank. eq formula is the latest formula in excel.

rank.eq vs rank formula in excel
Rank. Eq is the latest version of the rank formula itself


Now, the rank average function builds on the rank. eq function, for its shortcomings. Which, is nothing apart from duplicate rankings.

For eg; using the previous example you can see, that video 1 & 2, have the same rank. However, all that the rank.avg will do is to to give your duplicate values an average rank.

So let’s apply the rank. avg formula in excel

rank.avg versus rank.eq formula in excel
rank. avg gives average position to duplicate values

Disadvantages of Rank formula

The rank function, as we saw above suffers from giving duplicate values, the same rank. However, ideally, that shouldn’t really be a disadvantage, because we are only considering one set of data, which is views.

Now, what if we had one more parameter? It is still possible to get duplicate ranks, however, the probability of getting duplicate rankings reduces as we keep on increasing parameters.

Solution for Duplicate Ranks

So let me illustrate this point.

Ranking with multiple parameters
Adding parameters

So, now you can see that after adding the watch time as a second parameter the ranks have changed.

average of both the ranks
Taking the average of ranks

So, once you get the two average ranks then you can average it and get the final rank based on two parameters.

Other Methods for Ranking in Excel

Now, the only advantage that the rank formula provides, is the integer value of the rank. Else the easiest method to rank is using the sort function in excel. However, as you can try it for yourself and see that

ranking data using the sort formula in excel


The rank formula in excel is a useful tool for dealing with data, which needs a formula instead of just table sorting. Mostly, this can be useful, when you are creating custom dashboards instead of pivot dashboards. So, a good tool in your excel kitty to know, in situations where you need them.

Further Readings