Excel Ranking Function (How to Rank Values in Excel)

Excel ranking function – The Excel RANK function is a basic Excel function used to rank, calculate ranks, and sort data. It accurately ranks numbers in Excel and assigns the same rank to duplicate values.

Let's learn about the Excel ranking function.

CONTENTS

Excel Ranking Basics

Use the RANK function to calculate the rank of a number.

RANK Function

Let’s look at the definition and syntax of the ranking function.

Definition

This Excel function is used to calculate the rank of a specific value within a given data range. It sorts data based on the specified criteria and returns the rank by determining whether the value is the largest or smallest.

Syntax

= RANK(number, ref, [order])

number: Specifies the value whose rank you want to calculate.
ref: Specifies the range containing the data to rank.
order: Optional. Specifies how to calculate the rank. Enter 0 or omit it to rank in descending order, or enter 1 to rank in ascending order.

Excel Ranking Example (Including Ties)

Let’s look at a basic ranking example using the RANK function.

As shown below, ranks were assigned to sales quantities by salesperson.

Cell D3 =RANK(C3,$C$3:$C$13,0)

An important point in the formula is the use of absolute references.

A basic ranking was calculated using the Excel ranking function.

Excel Ranking Example (Without Ties)

When using the basic ranking method, you may see duplicate rank values.

One of the most efficient ways to identify duplicate values in Excel is to use “Conditional Formatting,” as shown below.

Duplicate values were highlighted using Conditional Formatting.

The formula to calculate ranks without duplicate values in Excel is as follows.

Cell D3 =RANK(C3,$C$3:$C$13,0)+COUNTIFS($C$3:C3,C3)-1

This formula uses the COUNTIFS function to return the original rank until a duplicate is found within the range from the first value to the current row. When a duplicate is found, it adds 1 to the rank, placing duplicate rank values in row order.

Because the Excel ranking function returns duplicate ranks, the COUNTIFS function is used to remove duplicate ranks.

Excel Ranking Example (Ranking Within a Rank)

The formula for converting ranks calculated from sales quantities into ranks within each salesperson is as follows.

Cell E3 =COUNTIFS($B:$B,$B3,$D:$D,”<“&D3)+1

This formula assigns rank 1 to the smallest rank for each salesperson and adds 1 to subsequent ranks.

Ranks within ranks can be calculated by combining the RANK and COUNTIFS functions.

Conclusion

Calculating ranks in Excel and removing duplicate values from those ranks is very useful for data analysis. This offers the following benefits.

Understanding the relative rank of data: You can use the RANK function to determine the relative rank of data. This helps you understand data distributions and characteristics for comparison and analysis.
Removing duplicate values: Removing duplicate values can improve data accuracy and consistency. You can use the RANK function to calculate the ranks of duplicate values and remove duplicate values to reduce redundancy and improve the reliability of analysis results.
Rank-based filtering: You can use the RANK function to sort data, calculate ranks, and filter for only data above or below a specific rank. This lets you extract and analyze only the desired range of data, reducing the volume of data and improving analysis efficiency.
Summarizing by rank: After using the RANK function to calculate positions or ranks for data, you can summarize the data by those positions or ranks. This helps you understand data characteristics and use them for comparison and analysis.

Therefore, using the RANK function to calculate ranks and remove duplicate values is very useful for data analysis. It can improve data accuracy and consistency while increasing analysis efficiency.