Excel RANK function

The Excel RANK function is a perfect tool for ranking values and is particularly useful for handling voluminous datasets. While analyzing a huge amount of data, you might have faced certain issues ranking particular values. 

As a data analyst myself, I use numerous Excel functions daily and can swear by the resourcefulness of the Excel RANK function.

While learning about this function, you’ll come across new terms like RANK.EQ and RANK.AVG, and yes, they can be overwhelming. So, I have designed this beginner-friendly tutorial covering all aspects, including these. 

What Is RANK Function?

Before delving straight into the facts, here’s some advice. This tutorial may confuse you if you have no Excel experience or knowledge. Go through a tutorial on MS Excel in that case.

On with the topic; Excel RANK function.

Let’s say you are tasked with publishing the rank list based on the results of more than 150 students in a school. Overwhelming task, isn’t it? This is where the RANK function comes in handy. Its ability to compare a huge amount of data and find out the highest among them all makes it a handy tool.

It ranks from top to bottom and vice versa. Here’s where you’ll find it:

• Under the “Formula” tab in the main tab, there is an option named “More Functions”.

• A drop down menu will appear under the ‘Statistical’ option showing two types of rank functions, ‘RANK.AVG’ and ‘RANK.EQ’.

Formula of the Rank Function

Building a proper formula for the function is the fundamental step, as the foundation matters the most. Just like other functions, the Excel RANK function has a formula, which is described below as the RANK formula :           

=RANK( number,ref,[order])

The arguments used above suggest the following things:

• Number: It is a mandatory argument where the number to rank is given.
• Ref: This argument refers to the set of cells from which you have to find the rank, which is also a mandatory argument.
• Order: An optional argument that gives you the option to rank it in ascending or descending order.

Here’s an example of how to set the formula:

 = RANK(A5,A5:A15,0)

If you want the ranks in ascending order, type 1 in the order section and 0 for descending order.

Types of Rank Functions

Excel RANK function is of two types: RANK.AVG and RANK.EQ.  Both functions have their own traits, which make them different from each other, but the only trait that unites them is the power to rank data. 

RANK.AVG 

RANK.AVG looks at the identical data and assigns an average rank to both the values. For example, say student A and B scored 85 marks and 85 is the 2 nd highest marks scored, hence they will be given a rank of 2.5.

RANK.EQ

RANK.EQ gives the same rank for both the values and omits the next rank. Taking the above example, it will give both students 2nd rank and skip 3 to give 4 rank. 

The formulas for the functions are identical. You can spot the difference between the two only when there are identical values in the provided data. Both the functions have their own unique way to rank duplicate values. 

How To Use Rank Function?

Dealing with the Rank function is easy and direct. Let’s go through a detailed yet simple visual on how to use it.

• Step 1: Make a table with the data which needs to be analysed.

• Step 2: Insert the rank function by clicking on “Formulas” in the main tab followed by more functions.

• Step 3: Select “Statistical” from the drop-down menu. You’ll find a list of other functions.

• Step 4: Click on RANK.AVG to rank the number.

• Step 5: A dialog box will appear where you have to fill in the ‘number,ref and order’. Click on ‘OK’ and you have the rank of the selected cell. In case, you want the ranks of the whole data click on the fill handle and drag it to the bottom of the data table and press Enter.

Example 

Here, I have highlighted some examples where I have used both the RANK functions 

For Ranking in A Sequence

Here’s an example to help you understand better. Let’s say, you have rank student scores in your class in descending order (highest to lowest).

• Step 1:  Create a table with appropriate data. 

• Step 2:  Click on the RANK.AVG function by following the steps mentioned above.
  

• Step 3:  A dialog box will appear. Fill the ‘number, ref, and order’ column with B2,B2:B10,0 (because descending order is the requirement)

Step 4:  Click on ‘OK’ and you will get the rank of B2 ie., Tina. Click on the ‘Fill Handle” (the small square visible at the bottom right) and drag it down to B10 to have the rank of all the students.

For Identical Values

Students achieving identical marks is common in school, but, it can be difficult for teachers to rank them. RANK.AVG is used in these cases.

Let’s say, Tina and Deepa scored 85 and you are tasked with ranking them. Follow the same steps as mentioned above and apply the same RANK formula. 

Now when you drag the fill handle you will be able to see the average rank given.

For Identical Values

When using the RANK.EQ function in Excel, if the data points are distinct, you'll receive the same ranking as shown in the example, following the conventional ranking method. 

However, when dealing with identical values, this Excel RANK function handles them differently. Let's see how with an example.

Let’s say, Tina and Deepa got the same score again. Follow the steps below: 

• Click on RANK.EQ and apply the formula
• The result will show both Tina and Deepa being given the 2nd rank with the 3rd rank being omitted.

Common Mistakes

I’ve made my fair share of mistakes when I first learned how to use the Excel RANK function. I’ve listed most of the common ones so, you can avoid them:

• Not scanning the data:  Double-check the data for identical values; if you have identical values, be thoughtful about which RANK function to use.

• Not changing the reference when adding new data: Whenever new data is added, it is mandatory to change to references and extend them according to the data.

•  Usage of random RANK Excel function: One of the most common mistakes is using any RANK function. You should be mindful about which function to use, as the results differ from each other.

• Neglecting order: Ignoring the order parameter in the RANK Excel function can significantly impact the output because, by default, it's set to 0. This can lead to inaccuracies in the results.

Summing It Up

The Excel RANK function is one of the nifty functions used for quickly rank values. Both RANK Excel functions are valuable for their own set of use cases and I can attest that they are used widely in data analysis. 

Speaking of data analysis, if you have half the mind to pursue it as a full-fledged career, don’t think twice. Go for it. It is a highly lucrative field with endless opportunities. If you are looking for a reliable course to start, head over to upGrad. As one of the best education platforms online, it offers top-tier programs affiliated to world-class institutions.

Frequently Asked Questions

1. How do you use the RANK function in Excel?

The RANK function can be used by clicking on the ‘RANK’ function option from the data tab and feeding the necessary info. A detailed method with step-by-step analysis is given above.

2. How do you write 1st, 2nd, 3rd in Excel?

You can easily rank 1st, 2nd and 3rd with the help of the Excel RANK function.

3. How do I sort RANK in Excel?

 After applying the rank function, right-click on the column and select Sort. You are then given a variety of methods to sort the ranks.

4. What is the difference between RANK EQ and RANK AVG?

 The major difference between RANK.EQ and Rank.AVG appears when identical values are present. RANK.EQ gives the same rank for identical values and skips the next one, whereas the latter one provides an average of the rank.

5. How do you calculate RANK?

RANK can be calculated using the rank formula under the RANK Excel function. By providing arguments such as number,ref, and order, you can calculate the rank of any data.

6. What is an example of RANK?

Many examples of RANK and its usage are explained in detail in this tutorial.