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 Statistical Functions in Excel Excel Formula For Rank
 

Excel Formula For Rank

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 8, 2023

Excel Formula for Rank

 

 

 Excel Formula For Rank (Table of Contents)

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • Introduction to Excel Formula For Rank
  • Steps to Find Largest to Smallest Rank

Introduction to Excel Formula For Rank

How do you find the rank of students, the rank of teams, and the rank of your employees? Finding rank isn’t the hardest task, and if there are no duplicate values, then the problem arises. In the corporate world, we often stress an employee’s rank among his group or overall rank at the company level. Regarding the educational sector, rank is not just a number but pride, prestige, and life for somebody.

This article will discuss finding an Excel Formula For Rank for your data. We can find Excel Formula For Rank in several ways, and we will explore each in today’s article.

Steps to Find Largest to Smallest Rank

We can also find the rank of teams by using the sorting option. Sorting is nothing but arranging things in order like Smallest to Largest, Largest to Smallest, and using this same concept; we can draw ranks.

You can download this Formula for Rank Excel Template here – Formula for Rank Excel Template

Now we will find Rank using Sort Largest to Smallest with the below steps. Let’s consider the below data of the IPL team’s table points.

Excel Formula for Rank Example 1-1

Step 1: Select the entire data range. Go to Data > Sort

Excel-Formula-for-Rank-Example-1-2

Or else you can press ALT + D + S (Do not hold any key type one at a time).

Step 2: Now, you will see the below dialog box.

Excel Formula for Rank Example 1-3

Step 3: Now, under Sort, select the option Points.

Excel Formula for Rank Example 1-4

Step 4: Under Order, select “Largest to Smallest.”

Excel Formula for Rank Example 1-5

Step 5: Click on OK to find the result.

Excel Formula for Rank Example 1-5

Step 6: The Output will be as shown below.

Excel Formula for Rank Example 1-6

Step 7: Insert serial numbers from 1 to 8 in the Rank column.

 Example 1-7

This is not the traditional way of finding Rank in Excel. We have a built-in function called RANK.EQ to find Ranks. I have shown just how to use Excel tools as an alternative to one another.

Find Rank Using RANK.EQ Function in Excel

Microsoft has a built-in function called RANK.EQ function, which can find the rank of a number among a series of numbers.

RANK Formula in Excel

Below is the RANK Formula in Excel:

Example 1-12

RANK.EQ formula in Excel has 3 arguments; out of those 3, two are mandatory arguments, and one is optional.

  • Number: This is the number you are trying to find the rank among many other values.
  • Ref: This is a range of numbers. We are trying to find the rank for NUMBER out of this range of numbers.
  • [Order]: This is an optional argument. Here you can specify the ranking of your number, whether it is in Ascending or Descending order. Zero (0) is for descending, and One (1) is for ascending order.

Let’s apply this formula to find the rank for 8 IPL teams, i.e., =RANK.EQ (B2, $B$2:$B$9)

 Example 1-8

Here RANK. The EQ function returned the same ranking for the teams RR & DC. The RANK function returns the same rank for duplicate values and ignores the subsequent ranking number.

 Example 1-9

Find RANK for Duplicates in Excel

We have seen RANK. The EQ function has returned the same ranking for duplicate numbers. We can overcome this limitation by using the COUNTIF function with RANK.EQ function. The formula used here is =RANK.EQ (B2, $B$2:$B$9) +COUNTIF ($B$2:B2, B2)-1

 Example 1-10

Firstly, RANK. The EQ function returns the rank as usual. Then COUNTIF function counts the current cell value in the current range and deducts 1 from the result. In the first case, RANK.EQ function returns 5; as a result, COUNTIF returns 1, and overall, our value will be 5.

Look at cell D6 by the time formula reaches cell D6, RANK.EQ returned 7 as a result. COUNTIF returned 2 as the result because COUNTIF found 2 counts of number 14 by the time it reached cell D6, and as usual, one will be deducted from 2, and the RANK will be placed as 8.

 Example 1-11

Things to Remember About Excel Formula For Rank

  • Do not use the RANK function because RANK replaces it.EQ in 2010 and later versions.
  • Sorting and finding the ranking is the non-traditional way of finding the rank in Excel.
  • EQ returns the same ranking for duplicate values by ignoring the next ranking slot. For example: If two numbers are supposed to rank as 14 & 15, RANK.EQ function will return as 14 for both the values by ignoring the next rank slot, 15.
  • If you want to slot ranks in ascending order, specify the order as 1. By default, it takes zero as the argument.

Recommended Articles

This is a guide to Excel Formula For Rank. Here we discuss how to use autofit in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Excel Formula for Weekday
  2. RANK Excel Function
  3. Excel Evaluate Formula
  4. Basic Excel Formulas

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 Formula for Rank Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Formula for Rank Excel Template

EDUCBA

डाउनलोड Formula for Rank Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW