EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Functions RANK Excel Function
 

RANK Excel Function

Dikshita Jain
Article byDikshita Jain
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated September 30, 2023

RANK Function in Excel

 

 

RANK in Excel

The RANK function in Excel shows where a number stands in a list. It helps identify if the number is the highest, lowest, or in between. For example, if students’ scores are 89, 96, and 78 in an exam, you can rank them as 2nd, 1st, and 3rd using RANK in Excel.

Here’s the basic syntax for RANK in Excel:

RANK Formula

This syntax includes two mandatory and one optional argument:

  • number: This is the value or number you want to find the rank for.
  • ref: This is the range of numbers you want to compare your “number” to.
  • [order] (optional): This indicates the ranking order as follows:
0 means descending (the highest value gets rank 1).
1 means ascending (the lowest value gets rank 1).

Types

In Excel, you can use three rank functions:

  1. RANK
  2. RANK.EQ
  3. RANK.AVG

If you start typing “rank” in Excel, it displays all three options.

RANK Formulas

You can choose the rank in Excel function that fits your needs best. Here’s what each rank function in Excel does:

Types 2

How to Use the RANK Function in Excel?

Let’s understand how to use rank in Excel through practical examples. We have covered each type for a clear understanding.

You can download this RANK Function in Excel Template here – RANK Function in Excel Template

Example #1: RANK Function (For Different Values)

Consider a list of students’ scores in a Math exam. We want to rank the student with the highest score as 1, and so on, based on their rankings.

Example #1

Solution:
Step 1:

  • In a new column (Column C), type this formula in cell C2:
    =RANK(B2, $B$2:$B$6, 0)

Example #1 Step 1

Step 2:

  • Press Enter after typing the formula.
  • It shows Alice’s rank as 4.

Example #1 Step 2

Step 3:

  • Select cell C2, then drag the small square that you see at its bottom right corner downwards.
  • Column C will now show all rankings.

As you can see in the image below, David, with a score of 96, ranks 1, and Carol, scoring 78, ranks 5.

Example #1 Step 3

Note: The formula breakdown is as follows:

  • B2: Represents the first student’s score (e.g., Alice’s score).
  • $B$2:$B$6: It is the range holding all scores. We have used absolute references to lock this range when copying the formula.
  • 0: indicates that we want to rank in descending order (the highest score will get rank 1).

Example #2: RANK Function (For Identical Values)

Now, we have a sales dataset where two products, Product B and Product D, show the same sales value of $150 Million. Let’s rank these products accordingly. 

Note: We will use “0” as the 3rd argument in the formula. Therefore, excel will rank in descending order.

Given:

Example # 2

1. In cell C2, type this formula:
=RANK(B2, $B$2:$B$6, 0)

Example # 2.1

2. When you press Enter, Excel will show the rank as 5 for the first product.

Example # 2.2

3. Drag the fill handle to extend the formula for the other products. The rank for each product will be displayed as follows.

Example # 2.3

Why there isn’t a 4th rank?

Excel doesn’t assign a 4th rank due to the tie between the sales value of Product B, and Product D. Excel assigns them both rank 3 and skips rank 4.

So, in this case:

  • Products B and D share the 3rd rank.
  • Product A gets the 5th rank.

Example #3: RANK.EQ Function (For Identical Values)

We have a dataset for a race where two athletes, John and Daniel, finish in 10.5 seconds. Let’s rank them using the RANK.EQ function in Excel.

Note: We will use “1” as the 3rd argument in the formula. Therefore, excel will rank in ascending order.

Given:

Example #3

Solution:
1. In cell D2, type the formula:
=RANK.EQ(B2,$B$2:$B$6,1)

Example #3.1

2. After pressing Enter, Excel calculates John’s rank as 1.

Example #3.2

3. Copy the formula to other cells to apply it to all athletes in the list.
Ranks for all athletes will be as follows:

Example #3.3

Result:

  • John and Daniel share the same time (10.5 seconds); therefore, both get rank 1.
  • Michael is ranked 3 because Excel skips rank 2 due to the tie between John and Daniel.

Example #4: RANK.AVG Function (For Different Values)

We have a list of student names and their science exam scores. Let’s use the RANK.AVG function to rank these students based on their scores.

Given:

Example #4

  1. In cell C2, enter the following formula:
    =RANK.AVG(B2,$B$2:$B$7,0)
  2. Press Enter. Excel will calculate the rank for Alice as 5.
  3. Copy the formula in column C to rank the other students.

Example #4.1

Ranks for all students will be as follows:

Example #4.2

Result:

  • Emma has the highest score of 95 and gets rank 1.
  • Bob has the second-highest score of 92 and gets rank 2.
  • Both Alice and Carol scored 85. RANK.AVG recognizes this tie and assigns them an average rank of 3.5.
  • David has a score of 80 and gets rank 5.
  • Frank has the lowest score of 78 and gets rank 6.

Advanced Applications of RANK Functions

 1. RANK + COUNTIF Function

Criteria 1: To rank in descending order
Alice and Carol scored 85 in the Science exam. We want to assign them unique ranks, meaning they should have different ranks. We will use RANK and COUNTIF functions to do this. Also, we will rank the scores in descending order (the highest score will receive rank 1).

Given:

RANK + COUNTIF Function

Solution:

  1. Type this formula in cell C2: =RANK.EQ(B2,$B$2:$B$7,0)+COUNTIF($B$2:B2,B2)-1
  2. Press Enter after typing the formula. It shows Alice’s rank as
  3. Apply the formula to all other cells by dragging it down.
  4. Column C will now show the unique rank values.

Result:
Here’s how unique ranks are decided for the same score:

  • The ranking depends on the order in which individuals are listed.
  • For instance, Alice, listed before Carol, gets a higher rank (3rd) than Carol (4th).

RANK + COUNTIF Function 2

Note: The formula works like this:

  • EQ(B2,$B$2:$B$7): Determines B2’s position in column B.
  • COUNTIF($B$2:B2, B2): Counts how many times B2 appears in the list up to the current row.
  • Subtracting 1: Prevents rank skips for duplicate values. It means that each value gets a distinct rank.

Criteria 2: To rank in ascending order
John and Daniel both finished a 100m race in 10.5 seconds. We want to assign them unique ranks. Also, we will rank all the athletes in ascending order (the lowest time will receive rank 1).

Given: 

RANK + COUNTIF Function 2.2

  1. Type the following formula in cell C2: =RANK.EQ(B2,$B$2:$B$6,1)+COUNTIF($B$2:B2,B2)-1.
  2. Follow the same process as the previous example (steps 2, 3, and 4).

Result:
RANK + COUNTIF Function 2.3

2. RANK.EQ + COUNTIFS Function

Criteria: Rank in Excel based on Multiple Criteria
In this example, we have a list of students with their Science Scores (Main Criteria) in column B and Total Scores (Secondary Criteria) in column C. Alice and Carol’s Science Score is the same (85). However, their total scores differ (420 and 430, respectively). To handle this, we will use a combination of RANK.EQ function and COUNTIFS function to rank them fairly.

Given:

RANK.EQ + COUNTIFS Function

Solution:

  1. Enter this formula in cell D2:
    RANK.EQ($B2, $B$2:$B$7, 0) + COUNTIFS($B$2:$B$7, $B2, $C$2:$C$7, “>”&$C2)
  2. Press Enter after typing the formula.
  3. Alice ranks 4th because her Total Score (420) is lower than Carol’s (430).
  4. Copy and apply the formula to all other cells.
  5. Column D will now show the rank for multiple criteria.

Result:
This formula considers both Science Scores and Total Scores to determine ranks fairly. If a student excels in both categories (like Carol), they receive a better rank (3rd rank) compared to another student (Alice – 4th rank).

RANK.EQ + COUNTIFS Function 2

3. Rank Based on the Group

Criteria 1: Rank Highest to Lowest
Here, we will assign ranks (highest to lowest) to students within each group using the SUMPRODUCT function.

Given:

Rank Based on the Group 1

Solution:

  1. Enter this formula in cell D2 and press Enter:
    =SUMPRODUCT((C2=$C$2:$C$7)*(B2<$B$2:$B$7))+1

Note: This formula calculates the rank of each student’s science scores within their respective groups.

  1. Then, copy cell D2 and paste it down for all the rows where you have data (in this case, down to D7).

In column D, you will now have the group rank for each student within their respective groups.

Rank Based on the Group 1.2

Criteria 2: Rank Lowest to Highest
Here, we will assign ranks (lowest to highest) to athletes within each group using the SUMPRODUCT function.

Given:

Rank Based on the Group 2

Solution:
Use this formula and repeat the same step from above:
=SUMPRODUCT((C2=$C$2:$C$6)*(B2>$B$2:$B$6))+1

Rank Based on the Group 2.2

Things to Remember

  • The RANK function in Excel only accepts numerical values. Using anything else will result in a #VALUE! error.
  • If the number you are testing is not in the list, Excel displays a #N/A! error to indicate its absence.
  • RANK in Excel gives the same ranking in the case of duplicate values. To get unique ranks for duplicates, use the COUNTIF function.
  • The function doesn’t need sorting data in ascending or descending order; it works efficiently regardless of the data’s arrangement.

Recommended Articles

We hope this explanation of RANK in Excel clarifies your queries about this topic. You can also go through our other suggested articles –

  1. Excel Formula For Rank
  2. Percentile Rank Formula
  3. Excel Evaluate Formula
  4. SUMPRODUCT Formula in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download RANK Function in Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download RANK Function in Excel Template

EDUCBA

डाउनलोड RANK Function in Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW