Ranking data in Excel is incredibly helpful in identifying the top or bottom performers in a dataset. By ranking data from highest to lowest, you can quickly identify the best or worst performers, making it easier to analyze and draw insights from the data. Whether you are analyzing sales figures, employee performance, or any other type of data, ranking data in Excel can help you make informed decisions based on the data. In this article, we are going to explore 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.

**Table of Contents**hide

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

In this section, we are going to explore 12 different cases to rank highest to lowest. Going through the article you can easily rank highest to lowest according to your cases.

### 1. Rank Against Highest to Lowest with Singular Criteria in Excel

In this section, we are going to rank the highest to lowest having singular criteria. Here, we have used the **RANK function**. To do so we are going to follow the steps for the dataset below.

- Now we are going to write the following formula in the
**D5**cell and drag the fill button from**D5 to D11**.

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

The **RANK** function will in this instance rank the value in cell **C5 **according to its position in the range **C5:C11**, from smallest value to greatest. 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-smallest value in the range.

When we write the formula with the **$** symbol, we reference the range **C5:C11** in an absolute manner.

### 2. Rank Highest to Lowest with Singular Criteria with Ties

In this section, we will rank from highest to lowest having ties in the dataset. We have used the **RANK** function here also like in the previous example. To do so, we are going to use the data set given below and follow the steps mentioned below.

- Now we are going to write the following in the
**D5**cell then drag and drop the fill icon 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.

### 3. Rank Highest to Lowest with Tie Break

Now we are going to explore the highest to lowest ranking with tie breaks. Here we will use **RANK**, **IF**, and **COUNTIF** functions. We are going to use the following steps on the data below for that.

- First, we are going to create a regular ranking based on “Sales in 2023”, in the
**E5**cell, we will write the following formula and fill**E5**to**E11**by dragging and dropping the fill icon.

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

- Then to create tie breaks we are going to write the formula from below in cell
**F5**and drag the fill icon**F5**to**F11**to get the tie breaks.

`=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**.

- Now we are going to add Tie Break and Rank in the
**G5**cell to get the final rank. So we write the following in the**G5**cell.

`=E5+F5`

### 4. Rank 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. We have used the **RANK.EQ function** to rank from highest to lowest with singular criteria without tie-breaking.

- Now we are going to write the following in the
**D5**cell and then drag and drop the fill button**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.

### 5. Rank Highest to Lowest with Multiple Criteria

Here, we are going to show how to rank with multiple criteria. We are going to use **RANK.EQ** and **COUNTIFS** functions in this case. To do so, we are going to have to follow the steps given below along with the data set given below.

- Now we are going to write this formula below in the
**E5**cell and drag and drop the fill icon from**E5 to E11**.

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

**🔎**** 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**.

More specifically, 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**, accounting for any ties or duplicates in the range and taking into account the corresponding values in column** D**.

### 6. Rank Highest to Lowest with Multiple Criteria Using COUNTIFS and COUNTIF Functions

Now let’s take a dataset where we need to rank with multiple criteria like in case 3, but instead of tie-breaking individually, we are going to rank them in one go. We are going to use **COUNTIF** and **COUNTIFS** functions in this case. We are going to use the same dataset and follow these steps.

- We are going to write the following formula in the
**E5**cell.

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

**🔎**** 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 **C5** and also have a value greater than the value in 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**.

### 7. Rank Highest to Lowest with Multiple Criteria Using SUMPRODUCT Function

With the same dataset, we are going to rank from highest to lowest with multiple criteria using **SUMPRODUCT** and **COUNTIF** functions by using the dataset below by following the step below.

- Now we are going to write the following formula in the
**E5**cell.

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

**🔎**** 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.

By adding the results of the** COUNTIF** and **SUMPRODUCT** 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** D5** and also have a value greater than the value in cell **C5**.

### 8. Rank Highest to Lowest with Percentiles

Here, we are going to rank them with percentiles. The highest sales will get 100%. To do so, we have to follow the steps given below and use the dataset below. Here we have used the **RANK.EQ **and **COUNT function**.

- We are going to write the following formula
**,**then drag and drop the fill button from**D5**to**D11**and use the**Percentage**option like indicated below**.**

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

**🔎**** 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.

### 9. Rank Highest to Lowest with Non-Adjacent Cells

In this section, we are going from highest to lowest with a non-adjacent dataset. Previously, we used the whole dataset, now we will use data with gaps. We have used **IFERROR** and **RANK** functions on the dataset below and you will follow the step below.

- Now we are going to write the following in the
**D5**cell, then drag and drop the fill button from**D5**to**D11**.

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

**🔎**** 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.

### 10. Rank Highest to Lowest According to Group in Excel

We are going to rank from highest to lowest according to the group now. To do so. we are going to follow the steps on the dataset below. We have used the **SUMPRODUCT** function to rank highest to lowest according to the group.

- We are going to write the following formula in the
**E5**cell and fill it from**E5**to**E11**by dragging the fill icon**.**

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

**🔎**** Formula Breakdown**

This formula calculates the rank of a particular value in a range, based on the criteria specified in the formula.

Here is a breakdown of the formula:

**=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.

### 11. Rank Highest to Lowest Ignoring Zeros

In some data you may see, some data are zeros. Using the Rank function can provide errors if the dataset has no value. So in this section, we are going to demonstrate how to rank with zeros by ignoring zeros. To rank highest to lowest ignoring zeros we have to use the dataset and follow the steps below. We have used **IF**, **RANK**, and **COUNTIF** functions to rank highest to lowest ignoring zeros.

- Now we are going to write the following formula in
**D5**and drag the fill button**D5 to D11**.

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

**🔎**** 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.

### 12. Rank Top 3 Sales Highest to Lowest in Excel

To rank the top 3 sales or 3 entries we have to follow the steps below using the dataset given below. We have used **LARGE**, **INDEX**, and **MATCH** functions.

- Now we are going to write the following formula in the
**G6**cell**.**

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

- Then we are going to write the formula in H6 and drag and drop the fill icon
**H6**to**H8**.

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

**🔎**** 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**.

### 13. Rank Bottom 3 Sales in Excel Highest to Lowest

Here we are going to discuss how to rank the bottom 3 sales. It is opposite to the main examples. In order to rank the bottom values, we are going to use this dataset along with the steps given below. Here we have used **SMALL**, **INDEX**, and **MATCH** functions.

- First, we are going to find the lowest 3 sales by using the below formula in the
**G6**cell.

`=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()**is an Excel function that returns the kth smallest value from a range of cells.**$C$5:$C$11**is the range of cells from which the kth 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.

- Now we are going to extract the names of the people having bottom 3 sales by using the formula.

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

**🔎**** 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()**t 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 Rank Lowest to Highest with Singular Criteria in Excel

In this section, we are going to rank highest to lowest which is the opposite of what we have been doing. In order to rank lowest to highest, we will follow the steps below. Here we have used the **RANK** function to rank lowest to highest.

- We are going to use the following formula in D5 to get the lowest to the highest ranking.

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

**🔎**** 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.

The **RANK() **function returns an integer that represents the rank of the value in cell C5 within the range **$C$5:$C$11**. If the function can not find the value in cell **C5** in the range, it returns the rank of the nearest value.

## Frequently Asked Questions

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

**Answer:** 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?**

**Answer:** 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?**

**Answer:** 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

- Use the
**RANK**function: To rank data in Excel, 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. - Provide two arguments: When using the RANK function, 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.
- Use the
**COUNT**function: 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**. - Deal 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. If you want to assign different ranks to tied values, you can use the**RANK.EQ**or**RANK.AVG**functions instead. - Absolute references: 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. - Custom formatting: 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.

## Conclusion

In conclusion, using Excel to rank data from highest to lowest is an effective technique for examining and comprehending huge datasets. Users can give each value in a range in Excel a rank based on how it compares to the other values in the range by using the **RANK **function. Users can easily find the highest-performing or most significant values in their dataset by using the **RANK **function with the third argument set to **FALSE**, which will rank values in decreasing order.

When working with financial, sales, or marketing data, this tool is very helpful because it helps you decide which items, areas, or customer segments are the most lucrative. Users can quickly and easily complete this process by adhering to the straightforward instructions described in this guide.