In this article, we’ll use a dataset of sales from some sales representatives to demonstrate how to rank in Excel highest to lowest in 13 different cases.

**Download Practice Workbook**

You can download the Excel workbook that we used to prepare this article.

## How to Rank in Excel Highest to Lowest (13 Handy Examples)

### Method 1 – Rank from Highest to Lowest with Singular Criteria in Excel

Let’s rank the salespeople based on the total sales in column C, sorting from highest to lowest.

- Apply the following formula to
**D5**and drag the fill handle from**D5 to D11**.

`=RANK(C5,$C$5:$C$11)`

The **RANK** function will rank the value in cell **C5 **according to its position in the range **C5:C11**, from the highest value down. The function will return the rank of the value in cell **C5** as a number. For instance, the function will return **2** if cell C5 contains the second-largest value in the range.

When we write the formula with the **$** symbol for a cell, we put an absolute reference to the range **C5:C11** so it doesn’t change with AutoFill.

### Method 2 – Rank from Highest to Lowest with Singular Criteria with Ties

Let’s use a similar approach to account for ties in the dataset, such as the rows 9 and 10 in the following dataset.

- Apply the following formula in
**D5**and drag and drop the fill handle to**D11:**

`=RANK(C5,$C$5:$C$11)`

**RANK(C5,$C$5:$C$11)**ranks the value in cell

**C5**against a range of values from cells

**C5**to

**C11**. The

**RANK**function assigns a rank to the value in cell

**C5**based on its position compared to the other values in the range.

In this case, we have ties in **Bruce(B9)** and **Clark(B10), **so they are ranked the same.

### Method 3 – Rank from Highest to Lowest with Tie Break

Let’s expand the previous dataset with past year’s sales to use for breaking any ties in ranking.

- Create regular ranking based on “Sales in 2023.” In the
**E5**cell, apply the following formula and fill from**E5**to**E11**by dragging and dropping the fill icon:

`=RANK(C5,$C$5:$C$11) `

- To create tie breaks, apply the following formula in cell
**F5**and drag the fill icon**F5**to**F11**.

`=IF(COUNTIF($C$5:$C$11,C5)>1,RANK(D5,$D$5:$D$11,0)/100,0)`

** Formula Breakdown**

The formula **=IF(COUNTIF($C$5:$C$11,C5)>1,RANK(D5,$D$5:$D$11,0)/100,0)** checks if there are any duplicates of the value in cell **C5 **within the range **$C$5:$C$11**. If there are duplicates, it calculates the rank of the value in cell **D5**, relative to the range of values **$D$5:$D$11**, and divides the result by **100**. If there are no duplicates, the formula returns **0**. More specifically, the **COUNTIF **function counts the number of times the value in cell **C5 **appears in the range **$C$5:$C$11**. If this count is greater than **1** (indicating that there are duplicates), the **RANK **function calculates the rank of the value in cell **D5**, relative to the range **$D$5:$D$11**, in descending order (the **0** as the third argument specifies this). The resulting rank is then divided by **100**. If the count is not greater than **1**, the formula returns **0**.

- Add the Tie Break and Rank in the
**G5**cell to get the final rank by applying the following formula in the cell:

`=E5+F5`

- AutoFill column G based on cell G5.

### Method 4 – Rank from Highest to Lowest with Singular Criteria Without Tie Breaking

We are going to use the dataset given below to rank the dataset without any tie breaking.

- Copy the following formula to cell
**D5**, apply it, and then drag and drop the fill icon from**D5**to**D11:**

`=RANK.EQ(C5,$C$5:$C$11)+COUNTIF($C$5:C5,C5)-1`

** Formula Breakdown**

The **RANK.EQ **function calculates the rank of the value in cell** C5** relative to the range** $C$5:$C$11**, where equal values receive the same rank and the next rank is skipped. The** COUNTIF **function counts the number of times the value in cell **C5** appears within the range **$C$5:C5** (up to the current row), and subtracts **1** from that count. This adjustment ensures that if there are duplicates of the value in cell **C5 **above it in the range, the rank is adjusted accordingly.

By adding the result of the** RANK.EQ** function to the result of the **COUNTIF** function (minus **1**), the formula calculates the final rank of the value in cell **C5**, accounting for any ties or duplicates in the range.

### Method 5 – Rank from Highest to Lowest with Multiple Criteria

Let’s use two years’ worth of sales to determine the rankings.

- Copy this formula in the
**E5**cell:

`=RANK.EQ($C5,$C$5:$C$11)+COUNTIFS($C$5:$C$11,$C5,$D$5:$D$11,">"&$D5)`

- Press
**Enter**and drag and drop the fill icon from**E5 to E11**.

** Formula Breakdown**

The formula calculates the rank of the value in cell **C5**, taking into account ties or duplicates within the range** $C$5:$C$11** and considering the corresponding values in column **D**.

The **RANK.EQ **function calculates the rank of the value in cell **C5 **relative to the range **$C$5:$C$11**, where equal values receive the same rank and the next rank is skipped. The **COUNTIFS** function counts the number of times the value in cell** C5** appears within the range **$C$5:$C$11** and also checks the corresponding values in column** D** to see if they are greater than the value in cell** D5**. This adjustment ensures that if there are duplicates of the value in cell C5, the rank is adjusted accordingly based on their corresponding values in column **D**.

By adding the result of the **RANK.EQ** function to the result of the **COUNTIFS** function, the formula calculates the final rank of the value in cell** C5**.

### Method 6 – Rank from Highest to Lowest with Multiple Criteria Using COUNTIFS and COUNTIF Functions

Let’s take a dataset where we need to rank with multiple criteria like in Method 3, but instead of tie-breaking individually, we are going to rank them in one go.

- Insert the following formula in the result cell
**E5:**

`=COUNTIF($C$5:$C$11,">"&$C5)+COUNTIFS($C$5:$C$11,$C5,$D$5:$D$11,">"&$D5)+1`

- Press Enter and AutoFill to the rest of the column.

** Formula Breakdown**

The formula counts the number of cells in the range **$C$5:$C$11 **that have a higher value than cell** C5** and the number of cells in the same range that have the same value as **C5** and also have a value greater than cell **D5**, and then adds **1** to the total count.

More specifically, the **COUNTIF** function counts the number of cells in the range** $C$5:$C$11** that is greater than the value in cell **C5**. The** “>&”** operator means “greater than”, so this function counts the number of cells in the range that have a value greater than the value in cell **C5**.

The **COUNTIFS** function counts the number of cells in the range **$C$5:$C$11** that have the same value as **C5** and also have a value greater than the value in cell** D5**. The** “&”** operator is used to concatenate the** “>” **operator and the value in cell **D5**, so the function counts the number of cells in the range that have a value greater than the value in cell **D5 **and also have the same value as **C5**.

By adding the results of the **COUNTIF** and** COUNTIFS** functions and adding **1** to the total count, the formula calculates the final count of cells in the range **$C$5:$C$11** that is greater than the value in cell **C5**, taking into account cells that have the same value as** C5** and also have a value greater than the value in cell **D5**.

### Method 7 – Rank from Highest to Lowest with Multiple Criteria Using the SUMPRODUCT Function

Let’s use a different formula for a similar dataset.

- Insert the following formula in
**E5:**

`=COUNTIF($C$5:$C$11,">"&$C5)+SUMPRODUCT(--($D$5:$D$11=D5),--($C$5:$C$11>C5))+1`

- Press
**Enter**and**AutoFill**to the entire column.

** Formula Breakdown**

The formula counts the number of cells in the range **$C$5:$C$11** that is greater than the value in cell **C5**, and also counts the number of cells in the same range that have the same value as **D5** and also have a value greater than the value in cell **C5**, and then adds **1** to the total count.

More specifically, the **COUNTIF** function counts the number of cells in the range** $C$5:$C$11** that is greater than the value in cell **C5**. The** “>&” **operator means “greater than”, so this function counts the number of cells in the range that have a value greater than the value in cell **C5**.

The **SUMPRODUCT **function is used to count the number of cells in the range **$C$5:$C$11** that have the same value as **D5** and also have a value greater than the value in cell **C5**. The “–” operator is used to convert the logical **TRUE**/**FALSE** values to **1**/**0** values, and then the two arrays are multiplied together and summed. This results in a count of the number of cells in the range that meet both criteria.

The formula then adds 1 since the counts will give a ranking one below the required. For example, the top result fulfills none of the first two criteria, so its rank becomes 1.

### Method 8 – Rank from Highest to Lowest with Percentiles

Let’s use the dataset for a single year’s sales and rank the salespeople as a percentile of the sample.

- Copy the following formula into the result cell
**D5**.

`=RANK.EQ(C5,$C$5:$C$11,1)/COUNT($C$5:$C$11)`

- Press
**Enter**and use the**Fill Handle tool**to**AutoFill**to the other cells in the column.

** Formula Breakdown**

The formula calculates the relative rank of the value in cell **C5 **within the range **$C$5:$C$11**. The “1” argument in the **RANK.EQ **function specifies that ties are ranked as the average of the ranks of the tied values. The result of the **RANK.EQ** function is divided by the total count of values in the range** $C$5:$C$11 **to normalize the rank.

The formula is used to compare the value in cell **C5** to the other values in the range **$C$5:$C$11** and determine its relative position. The result will be a number between **0 **and **1**, where a value of 0 means that the value in cell **C5** is the smallest value in the range, and a value of **1 **means that the value in cell **C5 **is the largest value in the range. The relative rank of a value can be useful in analyzing data and making comparisons between different values.

### Method 9 – Rank from Highest to Lowest with Non-Adjacent Cells

In this section, we are going from highest to lowest with a non-adjacent dataset. We will use the same dataset but use a limited selection.

- Copy the following formula to
**D5:**

`=IFERROR(RANK(C5,($C$5,$C$11,$C$9)), "")`

- Press
**Enter**and use the**Fill Handle**to**AutoFill**to the rest of the column.

** Formula Breakdown**

The formula returns the rank of the value in cell** C5** within the range** $C$5,$C$11,$C$9**. If the rank can’t be calculated, the formula returns an empty string (“”).

The **RANK **function calculates the rank of the value in cell **C5** within the specified range, where the highest value has a rank of** 1**, the second highest has a rank of** 2**, and so on. The range is specified as an array containing the values **$C$5, $C$11**, and** $C$9**. If the value in cell **C5** is not found in the specified range, the **RANK **function will return an error.

The **IFERROR** function is used to handle the error that may occur if the value in cell **C5** is not found in the specified range. If an error occurs, the** IFERROR** function returns an empty string (“”) instead of the error message. This can be useful to avoid displaying an error message to the user and to make the output more visually appealing.

### Method 10 – Rank from Highest to Lowest According to a Group in Excel

Let’s put the salespeople in two groups (A and B, listed in column B) and rank them within their group.

- Copy the following formula to cell
**E5****.**

`=SUMPRODUCT((B5=$B$5:$B$11)*(D5<$D$5:$D$11))+1`

- Press
**Enter**and use**AutoFill**on other cells in the column.

** Formula Breakdown**

**=SUMPRODUCT((B5=$B$5:$B$11)*(D5<$D$5:$D$11))+1**

**SUMPRODUCT():**This is a function that multiplies corresponding values in arrays and returns the sum of those products.**(B5=$B$5:$B$11)**: This is an array that returns TRUE or FALSE for each cell in the range B5:B11, depending on whether the value in that cell is equal to the value in B5.**(D5<$D$5:$D$11)**: This is another array that returns TRUE or FALSE for each cell in the range D5:D11, depending on whether the value in that cell is less than the value in D5.***(asterisk)**: This operator is used to multiply the two arrays element-wise, which means it multiplies the corresponding elements in both arrays and returns an array of the same size as the original arrays.**+1**: This adds 1 to the sum of the products of the two arrays.

### Method 11 – Rank From Highest to Lowest Ignoring Zeroes

The Rank function can provide errors if the dataset has no value. Let’s insert a zero value in the dataset and use a modified formula to avoid ranking it.

- Copy the following formula into
**D5.**

`=IF($C5=0,"",IF($C5>0,RANK($C5,$C$5:$C$11),RANK($C5,$C$5:$C$11)-COUNTIF($C$5:$C$11,0)))`

- Press
**Enter**and drag the fill icon from**D5 to D11**.

** Formula Breakdown**

**IF($C5=0,””,IF($C5>0,RANK($C5,$C$5:$C$11),RANK($C5,$C$5:$C$11)-COUNTIF($C$5:$C$11,0)))**

**IF($C5=0,””,…)**: This is an outer**IF**statement that checks if the value in cell**C5**is equal to zero. If it is, it returns an empty string (“”). If it isn’t, it proceeds to the inner**IF**statement.**IF($C5>0,…)**: This is an inner**IF**statement that checks if the value in cell**C5**is greater than zero. If it is, it returns the rank of the value in the range**C5:C11**. If it isn’t, it proceeds to the else part of the IF statement.**RANK($C5,$C$5:$C$11)**: This function returns the rank of the value in cell**C5**, compared to the values in the range**C5:C11**.**RANK($C5,$C$5:$C$11)-COUNTIF($C$5:$C$11,0)**: This function returns the rank of the value in cell**C5**, compared to the values in the range**C5:C11**, and subtracts the count of cells in that range that contain zero.

If the sales figure in cell **C5** is zero, the formula returns an empty string (“”). If it’s greater than zero, the formula returns the rank of that value in the range **C5:C11**. Lastly, if it’s less than or equal to zero, the formula returns the rank of that value in the range **C5:C11** but subtracts the count of cells in that range that contain zero. This ensures that zero values are ranked lower than non-zero values.

### Method 12 – Rank Top 3 Sales from Highest to Lowest in Excel

Let’s extract the three best performers from the dataset.

- Sort the table with one of the previous methods.
- Insert the following in cell
**G6**.

`=LARGE($C$5:$C$11, $F6)`

- Input the following formula in H6.

`=INDEX($B$5:$B$11,MATCH(G6,$C$5:$C$11,0))`

- Apply both formulas and use
**AutoFill**to fill the other rows.

** Formula Breakdown**

**= INDEX($B$5:$B$11, MATCH(G6, $C$5:$C$11, 0))**

**INDEX()**: This is a function that returns a value from a specified row and column in a range.

**$B$5:$B$11**: This is the range of cells that contains the values we want to return.

**MATCH()**: This is a function that returns the position of a value in a range.

**G6**: This is the value we want to find the position of in the range **$C$5:$C$11**.

**$C$5:$C$11**: This is the range of cells that contains the values we want to find the position of the value in **G6**.

### Method 13 – Rank Bottom 3 Sales in Excel from Lowest to Highest

Let’s get the worst performers from the sales team.

- Rank the table from highest to lowest using one of the methods above.
- Insert the following formula in cell
**G6**and apply it with**Enter**.

`=SMALL($C$5:$C$11, $F6) `

** Formula Breakdown**

The formula** SMALL($C$5:$C$11, $F6) **is an Excel formula that returns the **kth smallest value** from a range of cells. Here’s what each part of the formula means:

**SMALL()**returns the k-th smallest value from a range of cells.**$C$5:$C$11**is the range of cells from which the k-th smallest value will be returned. In this case, the range is fixed as an absolute reference using the dollar signs, meaning that the range will not change when the formula is copied to other cells.**$F6**is the k value that specifies which smallest value should be returned from the range. In this case, the**k**value is also fixed as an absolute reference, so it will not change when the formula is copied to other cells.

- Copy the following formula in
**H6**to extract the name based on the sales value.

`=INDEX($B$5:$B$11,MATCH(G6,$C$5:$C$11,0)) `

**AutoFill**both formulas down.

** Formula Explanation**

This formula finds a value in one range and returns the value from the other range that corresponds to that value. What each component of the formula signifies is as follows:

**INDEX()**returns a**value or reference**to a value from within a table or range.**$B$5:$B$11**is the range of cells from which the function will return a value. In this case, the range is fixed as an absolute reference using the dollar signs, meaning that the range will not change when you copy the formula to other cells.**MATCH()**searches for a specified item in a range of cells, and returns the relative position of the item within the range.**G6**is the value that the**MATCH()**function is searching for within the range**$C$5:$C$11**. In this case, the value is in cell**G6**.**$C$5:$C$11**is the range of cells in which the**MATCH()**function will search for the value**G6**. In this case, the range is fixed as an absolute reference using the dollar signs, meaning that the range will not change when the formula is copied to other cells.**0**is an optional argument that specifies the type of match we want to use. A value of 0 indicates an exact match.

## How to Rank Lowest to Highest with Singular Criteria in Excel

Let’s rank the team from lowest to highest.

- Use the following formula in
**D5**to get the lowest to the highest ranking.

`=RANK(C5,$C$5:$C$11,1)`

**AutoFill**to the other cells in the column.

** Formula Breakdown**

We use the **RANK() **function in Excel to determine the rank of a value within a range of values. The formula** =RANK(C5,$C$5:$C$11,1) **specifically calculates the rank of the value in cell** C5** within the range of cells **$C$5:$C$11**, where the** smallest value** in the range is assigned a rank of** 1**.

Here’s a breakdown of the formula:

**C5**is the cell reference to the value for which we want to calculate the rank. In this case, it’s assumed that the formula is located in a different cell than C5.**$C$5:$C$11**is the range of cells in which we want to calculate the rank of the value in cell C5. The $ signs before the row and column references make this an absolute reference, meaning that it will not change when you copy the formula to other cells.**1**is an optional argument that indicates the order in which the function will calculate the rank. A value of 1 means that it will rank the smallest value in the range as 1, the second-smallest as 2, and so on.

## Frequently Asked Questions

**How do I rank data in Excel?**

To rank data in Excel, you can use the **RANK **function. This function assigns a rank to each value in a list based on its position relative to the other values in the list. The rank of a value is determined by comparing it to the other values in the list.

**How do I use the RANK function in Excel?**

To use the **RANK **function in Excel, you need to provide two arguments: the first argument is the value you want to rank, and the second argument is the range of cells containing the values you want to rank against. The function will return the rank of the value within the range.

**How do I rank data in Excel with ties?**

When there are ties in the data, meaning multiple values have the same value, the **RANK **function will assign the same rank to each of the tied values, and then skip the next rank.

For example, if two values tie for second place, it will rank them both as assigned a rank of 2, and the next value as a rank of 4.

If you want to assign different ranks to tied values, you can use the **RANK.EQ** or **RANK.AVG** functions instead. The **RANK.EQ** function assigns the same rank to tied values and the **RANK.AVG** function assigns an average rank to tied values.

## Things to Remember

- When using the RANK function, you need to provide at least two arguments: the first argument is the value you want to rank, and the second argument is the range of cells containing the values you want to rank against.
- To rank data from highest to lowest, you can use the
**RANK**function in combination with the**COUNT**function. The formula is**“=COUNT($A$1:$A$10)-RANK(A1,$A$1:$A$10)+1”.**This formula will return the rank of the value in**A1**within the range**A1:A10**, with the highest value being ranked as**1**. - When there are ties in the data, the
**RANK**function will assign the same rank to each of the tied values, and then skip the next rank. If you want to assign different ranks to tied values, you can use the**RANK.EQ**or**RANK.AVG**functions instead. - When using the
**COUNT**function in combination with the**RANK**function, it’s important to use**absolute references**(e.g.**$A$1:$A$10**) to ensure that the range doesn’t change when you copy the formula to other cells. - To make the ranked data easier to read, you can use custom formatting to apply different colors or styles to the top-ranked values. You can do this by selecting the cells containing the ranked data, and then clicking on “Conditional Formatting” in the “Home” tab of the Excel ribbon.

**<< Go Back to Excel RANK Function | Excel Functions | Learn Excel**