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


<< Go Back to Excel RANK Function | Excel Functions | Learn Excel

What is ExcelDemy?

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

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo