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.
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)
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.
