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 RANK Function
- The RANK.EQ Function
- The RANK.AVG Function
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.
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.
❸ 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.
➤ 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.
❸ 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.
❸ 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.
➤ 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.
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!
