Excel RANK Function

When you’re working with numerical data in Excel, it is important to understand how values are compared with one another. When examining final exam results of the student, company revenues for a given period, or athlete competition performance, ranking will quickly help you analyze and extract meaningful insights. Excel has built-in functions like RANK, RANK.EQ() and RANK.AVG() that helps you simplify this task. In this blog, we are going to go through how to utilize these ranking functions with real examples and best practices to help you avoid mistakes.

Table of Contents:

• What is the RANK Function in Excel?
• Syntax of RANK Function in Excel
• Advanced Rank Functions in Excel
• How to Use the RANK Function?
• How to Calculate Percentile Rank Using Excel?
• Difference Between RANK, RANK.EQ() and RANK.AVG() Functions
• Real-world Example
• Using the RANK Function with Ascending and Descending Order
• Tips to Combine RANK With Other Excel Functions
• Common Mistakes and Solutions to Avoid Them
• Best Practices
• Conclusion

What is the RANK Function in Excel?

When analyzing data, the RANK function is one of the most important functions in Excel. This function returns the rank of a number from a list of numbers. This is useful when we need to compare values like sales or test scores.

In simple words, the RANK function tells you the rank of a value compared with the other values in the list. So if a student had the second highest score, RANK will return 2.

Mastering the RANK function is very important to become a Microsoft Excel expert in 2025.

Advance Your Career with Excel

Master Excel from experts and boost your productivity with real-world projects.

Explore Program

Syntax of RANK Function in Excel

Below is the basic syntax of the RANK Function:

=RANK(number, ref, [order])

Parameters:

• number: The value whose rank you want to find.
• ref: The range of numbers in the array to compare
• order: It is an optional parameter, 0 is descending order ranking, 1 is ascending order ranking.

Advanced Rank Functions in Excel

Excel added two improved versions of the RANK() function in Excel 2010: RANK.EQ() and RANK.AVG(). To handle the tie in the data, these two functions can be used.

1. RANK.EQ() – Equal Ranking

Returns the rank of a number in the list of numbers same behaviour as RANK().

Tie handling: This will assign the same rank and skip the ranks of the tied value.

Syntax:

=RANK.EQ(number, ref, [order])

2. RANK.AVG() – Average Ranking

Calculates the average ranking for identical values.

Tie handling: Instead of skipping numbers, it calculates the average of the ranks of tied values.

Syntax:

=RANK.AVG(number, ref, [order])

How to Use the RANK Function?

Step 1: Prepare your dataset

Ensure that the column you are going to rank contains only numeric values. If it is not so, clean the column.

Step 2: Enter the formula

Select the cell where you want to display the rank and use the formula below.

=RANK.EQ(B2, $B$2:$B$5, 0)

Parameters:

• B2: This is the column in which the ranking has to be displayed.
• $B$2:$B$5: It is the fixed range of the column.
• 0: It is used for descending order, i.e, highest score = rank 1.

Step 3: Applying to all rows

To apply the formula to all rows, drag the fill handle down.

Step 4: Choose the sorting method

=RANK.EQ(B2, $B$2:$B$5, 1)

If the value is 0, then it is ranked in descending order (For example, a student with the highest mark gets rank 1). If the value is 1, then it is ranked in ascending order (For example, a student with the lowest mark gets rank 1).

How to Calculate Percentile Rank Using Excel?

Percentile rank is the relative position of a value within a dataset, it’s the percentage of values that are equal to or less than it. For example, if your exam score was in the 85th percentile, you scored better than 85% of the group.

There are two types of built-in functions:

1. PERCENTRANK.INC() Excel function

Provides the rank of a value in a data set as a percentage between 0-1, inclusive (0% or 100%).  

Syntax:

=PERCENTRANK.INC(arr, x, [significance])

Parameters:

• Array: It consists of a range of numbers.
• x: The value for which the percentile rank has to be calculated.
• significance: Number of decimal places to round off.

Use case: Use when you want an inclusive ranking where the lowest values return 0% and the highest values return 100%.  

2. PERCENTRANK.EXC() Excel function

Provides the percentile rank in a way that does not include endpoints, so values returned will be between 0-1, but will never return exactly 0 or 1. 

Syntax:

=PERCENTRANK.EXC(arr, x, [significance])

Use case: Use when you want to exclude the top and bottom values within a dataset.

Difference Between RANK, RANK.EQ() and RANK.AVG() Functions

Real-world Example

Consider a company that has employees from different departments. Each employee has their corresponding performance score calculated by the performance appraisal system. Now, the HR wants to rank the employee by using the performance score.

Step 1: To get started, open the sample employee performance data file (CSV format) in Excel. This file contains details such as employee names, their departments, and their performance scores. Here’s a preview of the data you will work with:

Step 2: Insert a new column called Rank (Descending Order).

Step 3: Under the Rank column, use this formula 

=RANK(C2, $C$2:$C$11, 0)

Step 4: Once the formula is entered, drag the formula for all rows. Based on the scores, Excel will assign ranks from the highest to the lowest scores.

Using the RANK Function with Ascending and Descending Order

Depending on the argument you provide, the RANK function can sort the data from either highest to lowest or lowest to highest.

Syntax:

=RANK(number, ref, [order])

RANK with Descending Order

This function assigns lower rank numbers to higher values. For example, the highest score obtained by a student = Rank 1. By default, descending order is followed.

Syntax:

=RANK(B2, $B$2:$B$4, 0)

RANK with Ascending Order

This function ranks the lowest values with the lowest rank numbers. For example, the lowest cost of a product = Rank 1. 

Syntax:

=RANK(B2, $B$2:$B$4, 1)

Tips to Combine RANK With Other Excel Functions

To filter, sort, and analyse data more dynamically, we can combine RANK() with other Excel functions. 

1. Combining RANK with IF

Use Case: If the conditions are satisfied, then it is ranked.

Formula:

=IF(B2="North", RANK(C2, C$2:C$10), "")

Tip: Only the rank of the rows where column B is “North” is returned.

2. Combining RANK with COUNTIF/COUNTIFS

Use Case: Though there are duplicate values, it provides unique ranks.

Formula:

=RANK(A2, A$2:A$10) + COUNTIF(A$2:A2, A2) - 1

Tip: This can be used to increment the duplicate values, which assigns a unique ranking to all values.

3. Combining RANK with INDEX-MATCH

Use Case: Used to return the value associated with a certain rank.

Formula:

=INDEX(A2:A11, MATCH(1, D2:D11, 0))

Tip: Based on the position, names/scores can be retrieved.

Common Mistakes and Solutions to Avoid Them

1. Using Relative Cell References in the Range

• Mistake: Writing =RANK(A2, A2:A10) without locking the range.
• Problem: The rank result becomes incorrect when the formula is copied down because the range shifts.
• Fix: Use absolute references like = RANK(A2, $A$2:$A$10) to fix the range.

2. Ranking Non-Numeric or Mixed-Type Data

• Mistake: Including cells with text, blanks, or errors in the range.
• Problem: The function returns #VALUE! or produces inaccurate ranks.
• Fix: Clean your data before applying RANK. Use =ISNUMBER(cell_reference) to check if values are numeric, or use filters to exclude blanks and text.

3. Assuming RANK Automatically Breaks Ties

• Mistake: Believing that RANK gives different positions to duplicate values.
• Problem: RANK and RANK.EQ() assigns the same rank to tied values, skipping the next positions.
• Fix: Use RANK.AVG() to average tied ranks, or create a custom formula using COUNTIFS() to assign unique ranks.

4. Confusing Ascending and Descending Order Settings

• Mistake: Leaving the third argument (order) empty without realizing the default is descending.
• Problem: You may get reversed rankings if ascending order is intended.
• Fix: Always specify the order explicitly:
  • Use 0 for descending (higher values get rank 1).
  • Use 1 for ascending (lower values get rank 1).

5. Incorrect Use of RANK in Filtered or Dynamic Ranges

• Mistake: Applying RANK to a filtered list without adjusting the range.
• Problem: RANK still includes hidden rows, which may distort rankings in visible data.
• Fix: Use SUBTOTAL or FILTER (Excel 365/2021) to create a dynamic list.

Best Practices

• Clean the data: Consider removing the blanks and non-numeric values.
• Utilize absolute references: Prevent shifting ranges when dragging formulas.  
• Drop duplicates: Use RANK.AVG() or a self-made tie-breaker logic. 
• Label ranks properly: Explain the rank order (increasing or decreasing) in the table header. 
• Verify the formulas: For better accuracy, cross-verify with manual calculations.
• Combine with conditional formatting: To show meaningful insights, visually emphasize top and bottom ranks.

Start Your Excel Journey – 100% Free

Learn the essentials of Excel through interactive lessons and practical examples.

Explore Program

Conclusion

Ranking data is an important feature in Excel, and the RANK function helps you do this easily. Excel also offers RANK, RANK.EQ() and RANK.AVG() functions to handle different ranking needs. You can create automatic reports by using these functions with others like IF, COUNTIFS, or ROUNDUP. Learning these skills will help you make faster and better decisions with your data. In this blog, you have learned all about the RANK function in detail.

To take your Excel skills to the next level, explore this comprehensive Excel training course and get hands-on practice. Also, get interview-ready with Excel interview questions curated by industry professionals.

RANK Function in Excel – FAQs