Sometimes in Excel, we have to work with the kind of data that might need to be **ranked** or **ordered** based on a specific set of criteria. For example, when you are trying to determine the positions of students in a class based on the scores they have achieved in multiple subjects or when you are trying to rank the participants of a particular game or competition based on different metrics of performance. While ranking such data, you will often come across the problem of having** multiple students or participants taking the same rank or position** due to scoring the same number in the exam or all the participants having the same level of performance. In such cases, we need to **break the ties in ranking to separate the participants**. In this tutorial, I will show you** how to rank with ties** in Excel.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice book to exercise the task while you are reading this article.

**5 Suitable Methods to Rank with Ties in Excel**

Let’s consider a situation where we have an Excel worksheet that contains information about the participants and their performance in a sporting contest. The Excel worksheet has the** Names** of the participants, their **Scores** in the contest, **Scores** they have achieved, and their **Ranks** based on the performance. The image below shows the **General Ranking** with ties between two participants and also the **Rank without Ties**. We will use this Excel worksheet to learn how to rank with ties in Excel

**1. Rank First Value in a Tie the Superior Position **

If we just consider only one criterion to rank the data in an Excel worksheet, then we can follow the below steps to rank with ties in Excel

**Step 1:**

- First, we will try to
**rank**the participants based on their**Scores**in the contest. For that, we will write the below formula in cell**E5**.

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

**Formula Explanation: **

- The
**RANK**function will take**two arguments**The**first one**is the cell value**C5**while the**second one**is an absolute range**$C$5:$C$14**. - The
**RANK**function will try to determine the**rank or order**of the cell value**C5**in the absolute range**$C$5:$C$14**.

**Step 2:**

- Upon pressing
**ENTER**, we will see that the participant in the first row (**C5**) has been ranked as**8th**. - We will now drag the fill handle of cell
**E5**to apply the formula to the rest of the cells.

- As all the participants have been ranked now, we will now see that 2 participants named Brad (
**Cell B6**) and Chris (**Cell B9**) are ranked in the same position (**Rank****4th**).

**Step 3:**

- We will now insert the below formula in cell
**F5**to**break the tie in the rank**.

`=RANK(C5,$C$5:$C$14)+COUNTIF(C$5:C5,C5)-1`

**Formula Explanation: **

**COUNTIF**function takes the*expanding range***C$5:C5**as the**first argument**and the cell value**C5**as the**second argument**. It will try to find out**how many times**the value in**C5**(**second argument**)**appears**in the**expanding range**(**first argument**).- The value returned by the
**COUNTIF**function will then be**added**with the value returned by the**RANK**function and**1**will be**subtracted**. - When this formula will be applied to the
**first tied rank**(**Cell E6**), the**COUNTIF**function will return**1**as rank**4**appears**only once**till that point. - As the
**value**(**1**) returned by the**COUNTIF**function will be**added**to the**value**returned by the**RANK**function while**1**will be**subtracted**, the**RANK**of the participant**Brad**will remain**unchanged**.- The value returned by the
**RANK**function =**4** - The value returned by the
**COUNTIF**function =**1** - Value to be subtracted =
**1** - So, the formula will return the value =
**4 + 1 – 1**=**4**

- The value returned by the

- In the case of the
**second tied rank**(**Cell E9**), the**COUNTIF**function will return**2**as rank**4**appears**twice**till that point. - As the
**value**(**2**) returned by the**COUNTIF**function will be**added**to the**value**returned by the**RANK**function while**1**will be**subtracted**, the**RANK**of the participant**Chris**will be increased to**5**. -
- The value returned by the
**RANK**function =**4** - The value returned by the
**COUNTIF**function =**2** - Value to be subtracted =
**1** - So, the formula will return the value =
**4 + 2 – 1**=**5**

- The value returned by the

- Upon pressing
**ENTER**, we will see that cell**F5**is showing**8**as the rank of the participant in cell**B5**. - We will now drag the fill handle of cell
**F5**to apply the formula to the rest of the cells.

- We will now see that the
**ranks of all the participants**have remained**unchanged**except for the two participants named**Brad**and**Chris**who shared the same rank before (**Rank 4**).^{th} **The rank**of the preceding participant named**Brad**remains**4**while the following participant^{th}**Chris**is now ranked**5**.^{th}

**Related Content:** **Rank IF Formula in Excel (5 Examples)**

**2. Use Two COUNTIF Functions Together to Break the Tie in a Rank in Excel**

Alternatively, we can also use two** COUNTIF** functions together to rank with ties in Excel. We have to follow the below steps. For this example, we will rank the participants based on the** Time** they passed to complete the task.

**Step 1:**

- First, we will rank all the participants based
**Time**they passed to complete the task. - After ranking all the participants, we will now see that 3 participants named Brad (
**Cell B6**) and Chris (**Cell B9**), and Tobey (**Cell B13**) are ranked in the same position (**Rank****4th**). - Now, we will write the below formula in cell
**E5**.

`=COUNTIF($D$5:$D$14,">"&$D5)+COUNTIF($D$5:D5,D5)`

**Formula Explanation:**

- The first
**COUNTIF**function will take theas the*absolute range*$D$5:$D$14**first argument**and the cell value**D5**as the**second argument**. It will use the**greater than**(**>)**sign to determine how many values in the(*absolute range*$D$5:$D$14**first argument**) is**greater than**the cell value**D5**(**second argument**). - The second
**COUNTIF**function takes the*expanding range***D$5:D5**as the**first argument**and the cell value**D5**as the**second argument**. It will try to find out**how many times**the value in**D5**(**second argument**)**appears**in the**expanding range**(**first argument**). - The
**two values**returned from the two**COUNTIF**functions will then be added. - The first
**COUNTIF**function is basically the**RANK**function but it will return a value**one**(**1**)**less**than the value returned by the**RANK**Because it will only return the**number**of those**values greater**than the cell**D5**while the**RANK**function will return**one**(**1**)**more**than that**number**. We all know that that is how we calculate the**RANK**or**ORDER**of something!!!! - The second
**COUNTIF**function is similar to the**COUNTIF**function used in**Method 1**. - So, the
**sum of the values**will be**similar**to the value returned by the**formula**in**Method 1**.

**Step 2:**

- Upon pressing
**ENTER**, we will see that cell**F5**is showing**4**as the rank of the participant in cell**B5**. - We will now drag the fill handle of cell
**F5**to apply the formula to the rest of the cells.

- We will now see that the
**ranks of all the participants**have remained**unchanged**except for the three participants named**Brad**,**Chris,**and**Tobey**who shared the same rank before (**Rank 4**).^{th} - The
**rank**of the first participant named**Brad**remains in**4**position while the following participant^{th}**Chris**is now ranked**5**and the last participant^{th}**Tobey**holds the**6**position^{th}**.**

**3. Use a Second Criteria to Break the Tie in a Rank in Excel**

If we have multiple criteria rather than one like above, then we can use the second one to** break the tie in a particular rank**. For example, we will use the Time to complete the

**Step 1:**

- First, we will write the below formula in cell

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

**Formula Breakdown:**

- The
**first argument**or the**logical test**for the**IF**function is**COUNTIF($C$5:$C$14,C5)>1**. So, the**IF**function will determine if the**number of appearances**of the cell value**C5**in theis*absolute range*$C$5:$C$14**greater than 1**. - The
**second argument**for the**IF**function is**what it will return**if the**logical test**is**TRUE**. We have inserted**RANK(D5,$D$5:$D$14,1)/100**as the**second argument**. So, the**RANK**function will determine the**rank of the participants**based on the**Time**they took to complete the task. The first argument for the**RANK**function is**Time**(**D5**), the second one is theof the*absolute range***Time**column**$D$5:$D$14**. And the**last argument**is**1**which tells the**RANK**function to rank the participants in**ascending order**. We will divide the**return value**of the**RANK**function by**100**to get a**decimal value less than 1**. - The
**third argument**for the**IF**function is**what it will return**if the**logical test**is**FALSE**. We have inserted**zero**(**0**) as the**third argument**. - Every value in the
or each value in the*absolute range*$C$5:$C$14**Score**column except for the value in**cell B6 and B9 (Score 73)**only appears once. So, the**COUNTIF**function will return**1**as the**number of the appearance**of the cell value**C5**is**1**. Hence, the**Logical Test**of the**IF**function will return**FALSE**and the function itself will return**0**. - But in the case of cells
**B6 and B9 (Score 73)**, the number of appearances is**2**. - Hence, the
**Logical Test**of**IF**function will return**TRUE**and the function itself will return a**decimal numbe**r that is the**return value**of the**RANK**function (Based on the**Time**column) divided by**100**. The**rank**for the two cell values will be different this time. So, we will get**two different decimal numbers**. These two different values will act as the**Tie Breaker**.

**Step 2:**

- Upon pressing
**ENTER**, we will see that cell**F5**is showing the value**zero**(**0**). - We will now drag the fill handle of cell
**E5**to apply the formula to the rest of the cells.

- Now, we will see that formula has populated each cell in the
**Tie Breaker**column with**zero**(**0**). But it omits the two cells that shared the same rank (**Rank 4**).^{th} - The
**preceding cell**has the value of**08**while the following cell is populated with a value of**0.01**.

**Step 3: **

- We will now write the below formula in cell
**G5**under the**Final Rank**

`=SUM(E5,F5)`

**Formula Breakdown:**

- The
**SUM**function will sum up the**existing rank value**(**E5**) and the second rank value based on the**Time**needed to complete the task (**F5**) to determine the**Final Rank**.

- Upon clicking
**ENTER**, we will get the**Final Rank**for the first participant. - We will now drag the fill handle of cell
**G5**to apply the formula to the rest of the cells.

- Finally, we will get the
**separate ranks for the two participants**as the**respective values**in the**Tie Breaker**column have been**added**to the**existing ranks**.

**4. Split the Available Winnings Specified For a Ranked Tied Between the Rank Holders **

Sometimes while** distributing the prize or points in a competition**, we might **not want to break the tie in a specific rank**. Instead, we will try to** distribute the prize or points specified **for that** rank **along with** succeeding occupied ranks **due to this tie** among** **all the participants** that **hold this same rank**. For example, we have **specified the prize for each rank**. We will now split the prizes specified for the **4 ^{th}**,

**5**and

^{th,}**6**positions among the

^{th}**3 participants holding the same 4**. As we have

^{th}positions**3 participants**holding the

**4**another

^{th}rank or position,**2 participants**are not available to hold the

**5**or

^{th}**6**position.

^{th}**Step 1:**

- First, we will write the below formula in the cell.

`=AVERAGE(OFFSET($I$4,E5,0, COUNTIF($E$5:$E$14,E5)))`

**Formula Breakdown:**

**OFFSET**function takes exactly**4 arguments**. The**first one**is the**cell reference**. In this case, it is the absolute reference to cell**I4**(**$I$4**)**.**So, the reference will not change when we drag the**fill handle**- The
**second argument**is the**number of rows****down**from the**cell**specified in the**first argument**(**I4**). In this example, it is the**respective value**in the**same row**of the**General Rank**(**E5:E14**). So, for the first cell**(cell F5**), it is**9 (cell E5)**.**OFFSET**will refer to the cell in the**Prize**column (**Column I**) that is**9 rows down**from cell**I4**. That cell is**I13**and this cell exactly refers to the**prize**specified for**rank 9**. As we drag the fill handle to apply the formula to**different cells**in this column (**Column F**), the**OFFSET**function will**rightly**refer to the**prize**that is assigned to the**respective rank**in that**row**. So, we will get the**prize**for each participant according to their**ranks**. **Third argument**refers to the**number of columns,**to the**left or right**, the function should refer to. In this case, it is**zero**(**0**). So, it will not refer to any left or right column.**The fourth argument**is a**COUNTIF**function that will find out**how many times**the value in**E5****appears**in the*expanding range*$E$5:$E$14**COUNTIF**function will tell the**OFFSET**function**how many rows**it should refer to or the**height of the reference**. In the case of the**3****cells**with**tied ranks**(**Cell F6**,**F9**,**F12**), it will return**3**. The**COUNTIF**function will find the**rank 4**in the*expanding range*3 times*.*So, in the case of these 3 cells, the**OFFSET**function will refer to**3 cells**:**I8**,**I9,**and**I10**as the**height**of the reference is**3**. These**3 cells**respectively refer to the**prize**assigned to the ranks**4**,**5,**and**6**.- Finally, the
**AVERAGE**function will average the cell values that the**OFFSET**function will refer to. In the case of the**3****cells**with**tied ranks**, it will**average**the values in 3 cells:**I8**,**I9**. So, it will average the prize money assigned to the ranks**4**,**5,**and**6**. Each participant holding the**tied ranks**will get this**average prize money**.

**Step 2:**

- Now, we will see that the first participant who ranked
**9**in this list has got^{th}**$20**.**Note that**, the prize specified for the**9**position was^{th}**$20**. - We will now drag the fill handle of cell
**F5**to apply the formula to the rest of the cells.

- Finally, we will find out that each of the 3 participants holding the
**4**position has got the^{th}**average**of the**3 prizes**specified for the**4**,^{th}**5**, and^{th}**6**.^{th}

**5. Use the SUMPRODUCT Function To Prevent Skipping Ranks in Excel**

If we do not want to break the tie in a rank. But also want the **RANK** function to **prevent** from **skipping the succeeding ranks**. For example, we have a tie in **5 ^{th} rank **with

**3 participant**s belonging to

**this rank**. But the

**RANK**formula has also skipped the

**6**and

^{th}**7**positions. The

^{th}**2**

**participants**supposed to

**hold these ranks**now belong to the

**5**. So, we want to have these

^{th}position**2 ranks occupied**but without

**breaking the tie in the 5**. We can use the below formula to achieve this objective.

^{th}position**Step 1:**

- First, we will write the below formula in cell

`=SUMPRODUCT((D5<=$D$5:$D$14)/COUNTIF($D$5:$D$14,$D$5:$D$14))`

**Formula Breakdown:**

The **SUMPRODUCT** function will multiply and then sum up the values in the **$D$5:$D$14** which are **greater than** or **equal** to cell value **D5** with the values returned by the **COUNTIF**.

**Note:**We have sorted our worksheet based on the existing General Rank before applying the above formula. Please see this tutorial on How to Sort and Filter Data in Excel.

**Step 2:**

- Upon clicking
**ENTER**, we will see that**rank of the first participant**in the list has remained**unchanged**. - We will now drag the fill handle of cell
**F5**to apply the formula to the rest of the cells.

- Now we will see that the
**tie in 5**has not been^{th}rank**broken**. But Excel has**not skipped**the**6**and^{th}**7**positions while ranking the participants.^{th} - We have now
**3 participants**holding only a**single position**along with any**succeeding rank**being**unskipped.**So, we will now have a total of**8 positions or ranks**instead of**10 ranks**for**10 participants**.

**Read More:** **Excel Percentile Rank Examples (7 Quick Ways)**

**Things to Remember**

- All of the methods above have used different
**built-in functions**to rank with ties in Excel. You should have a clear understanding of how the**functions work**. Otherwise, it will be very hard to use these functions to rank with ties in Excel. - The article has
**links to the tutorials on each of the functions**we have used. Please do visit the pages to learn about these functions.

**Conclusion**

In this article, we have learned to **rank with ties in Excel**. I hope from now on you can **rank with ties in Excel** very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!