How to Create an Auto Ranking Table in Excel (with Quick Steps)

Get FREE Advanced Excel Exercises with Solutions!

An auto ranking table can rank data based on ascending or descending order using a formula. In this article, you will learn to create an auto ranking table in Excel with some quick steps.


Watch Video – Create an Auto Ranking Table in Excel



Difference Between RANK, RANK.EQ, & RANK.AVG Functions

To create an auto ranking table, you can use any one of the 3 Excel functions.

The primary difference between these 3 functions is the capability to handle duplicate values while ranking.

The RANK & RANK.EQ functions do pretty much the same job. They give the lowest ranking to all the duplicate values. In the screenshot below, you can see both the RANK and RANK.EQ functions give rank 3 to the $12,000.

The RANK.EQ function is available only in Excel 2010 and later versions.

But the RANK function is available in all Excel versions.

But it is better to get used to the RANK.EQ function as the RANK function can be outdated at any time.

On the other hand, the RANK.AVG function given an average function ranking to all the duplicate values.

For example, both the $12,000 got a rank of 3.5 in the RANK.AVG column.

Difference Between RANK, RANK.EQ, & RANK.AVG Functions

Read More: How to Rank Average in Excel


How to Create an Auto Ranking Table in Excel : 3 Effective Methods

In this section, I will show 3 methods to create auto ranking table in Excel. Considering all the facts, I will be using the RANK.EQ function to demonstrate the steps for creating an auto ranking table in Excel.


1. Create an Auto Ranking Table for Ascending Order

In this method, I will show you to create an auto ranking table for ascending order using the RANK.EQ function.

To do that,

❶ First, insert the following formula in cell D5.

=RANK.EQ(C5,$C$5:$C$14,1)

❷ Then hit the ENTER button.

Create an Auto Ranking Table for Ascending Order

❸ Now drag the Fill Handle icon from cell D5 to D14.

Now you will see the relative ranking of the data from the Monthly Income column to the Ranking column.

Output: Create an Auto Ranking Table for Ascending Order

Formula Breakdown

➤ C5

This is the top cell of the range $C$5:$C$14.

➤ $C$5:$C$14

This is the range within which the ranking is performed.

➤ 1

This value refers to the ascending order.

➤ RANK.EQ(C5,$C$5:$C$14,1)

Here, the RANK.EQ function returns the relative ranking of C5 within the range $C$5:$C$14 based on ascending order.


2. Create an Auto Ranking Table for Descending Order

Here, I will show you the procedure to create an auto ranking table using the RANK.EQ function based on descending order.

For that,

❶ Insert the following formula in cell D5.

=RANK.EQ(C5,$C$5:$C$14,0)

❷ Now hit the ENTER button.

Create an Auto Ranking Table for Descending Order

❸ Now drag the Fill Handle icon from cell D5 to D14.

Now you will see the relative ranking of the data from the Monthly Income column to the Ranking column.

Formula Breakdown

➤ C5

This is the top cell of the range $C$5:$C$14.

➤ $C$5:$C$14

This is the range within which the ranking is performed.

➤ 0

This value refers to the descending order.

➤ RANK.EQ(C5,$C$5:$C$14,0)

Here, the RANK.EQ function returns the relative ranking of C5 within the range $C$5:$C$14 based on descending order.

Read More: Excel Percentile Rank Inc vs Exc


3. Handle Duplicate Values While Auto Ranking Table in Excel

In general, the RANK.EQ function gives the same ranking to all duplicate values. To solve this problem, we will incorporate the COUNTIF function with the RANK.EQ function.

Now follow the steps below:

❶ At first, insert the following formula in cell D5.

=RANK.EQ(C5,$C$5:$C$14)+COUNTIF($C$5:C5,C5)-1

❷ After that, press the ENTER button.

Handle Duplicate Values While Auto Ranking Table in Excel

❸ Now drag the Fill Handle icon from cell D5 to D14.

Now you will see the relative ranking of the data from the Monthly Income column to the Ranking column.

Result: Handle Duplicate Values While Auto Ranking Table in Excel

Formula Breakdown

➤ C5

This is the top cell of the range $C$5:$C$14.

➤ $C$5:$C$14

This is the range within which the ranking is performed.

➤ RANK.EQ(C5,$C$5:$C$14)

Here, the RANK.EQ function returns the relative ranking of C5 within the range $C$5:$C$14 based on descending order.

➤ COUNTIF($C$5:C5,C5)

Here, C5 of $C$5:C5 changes as you copy down the formula. Thus, the COUNTIF function compares C5 within the range $C$5:C5 for duplicate values. After that, it returns the count for the duplicate values.

➤ RANK.EQ(C5,$C$5:$C$14)+COUNTIF($C$5:C5,C5)-1

For any duplicate values, COUNTIF($C$5:C5,C5) returns the occurrence of the duplicates which is added to the rank returned by RANK.EQ(C5,$C$5:$C$14). Finally, 1 is subtracted to keep the original ranking of the data.


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


Conclusion

To sum up, we have discussed the necessary steps to create an auto ranking table in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. Goodbye!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo