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

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.


Download Practice Workbook

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


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 (4 Common Scenarios)


Steps to Create an 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.

Read More: How to Create a Ranking Graph in Excel (5 Methods)


Similar Readings


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 Formula to Rank with Duplicates (3 Examples)


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.

Read More: Ranking Based on Multiple Criteria in Excel (4 Cases)


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.


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. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

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

ExcelDemy
Logo