EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel Excel Formula For Rank
Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course

Excel Formula For Rank

By Jeevan A YJeevan A Y

Excel Formula for Rank

 Excel Formula For Rank (Table of Contents)

  • 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, rank of your employees? Finding rank isn’t the hardest task, and if there are no duplicate values, then the problem arises. Often in the corporate world, we stress on an employee’s rank among his group or overall rank at the company level. When it comes to the educational sector, rank is not just a number; it is pride, prestige, and life for somebody.

Start Your Free Excel Course

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

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

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,325 ratings)

Steps to Find Largest to Smallest Rank

We can find the rank of teams by using the sorting option as well. 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 IPL teams 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 by select the option Points.

Excel Formula for Rank Example 1-4

Step 4: Now, 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: In the Rank column, insert serial numbers from 1 to 8.

Excel Formula for Rank 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 to tell you the ways of using 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 series of numbers.

RANK Formula in Excel

Below is the RANK Formula in Excel:

Excel Formula for Rank Example 1-12

RANK.EQ formula in excel has 3 arguments, and out of those 3, two are mandatory arguments, and one is an optional argument.

  • Number: This is the number you are trying to find the rank among many other values.
  • Ref: This is a range of numbers. Out of this range of numbers, we are trying to find the rank for NUMBER.
  • [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.

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

Excel Formula for Rank Example 1-8

Here RANK.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.

Excel Formula for Rank Example 1-9

Find RANK for Duplicates in Excel

We have seen RANK.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

Excel Formula for Rank Example 1-10

Firstly, RANK.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 as a result, and overall, we value will be 5 itself.

Now, look at the 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 reaches the cell D6, and as usual, one will be deducted from 2, and the RANK will be placed as 8.

Excel Formula for Rank Example 1-11

Things to Remember About Excel Formula For Rank

  • Do not use the RANK function because it is replaced by RANK.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 very next ranking slot. For example: If two numbers supposed to rank as 14 & 15, RANK.EQ function will return as 14 for both the values by ignoring the very next rank slot 15.
  • Just in case if you want to slot ranks in ascending order, you need to 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 along with 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
Popular Course in this category
MS Excel Training Bundle
  13 Online Courses |  100+ Hours |  Verifiable Certificates |  Lifetime Validity
4.5
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Formula for Rank Excel Template

EDUCBA

Download Formula for Rank Excel Template

EDUCBA

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

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more