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}

**Read More:** **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}**.**

**Read More: Excel Formula to Rank with Duplicates (3 Examples)**

**Similar Readings**

**How to Calculate Weighted Ranking in Excel (4 Ways)****Excel Percentile Rank Inc vs Exc****How to Create an Auto Ranking Table in Excel (with Quick Steps)**

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

**Read More: Ranking Based on Multiple Criteria in Excel (4 Cases)**

**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}

**Read More: How to Rank Average in Excel (4 Common Scenarios)**

**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:** **How to Calculate Rank Percentile in Excel (7 Suitable Examples)**

**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 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!!!

Thank you for your EXCELlent website!

Wish you can help me with my problem, because I am at a loss…

I’m trying to create a scorecard for 20 players who play 22 games.

I need 24 tie breakers:

– 22 Tie breakers for the positions in which they have finished the game;

– 1 Tie breaker for the number of games they have played;

– 1 Arbitrary tie breaker in case everything is tied.

In case the points are tied, then the best position must be checked.

If also the best positions are tied, then the second best must be checked, etc. (up to 22 games).

Let’s say that after 3 games two players are both equal in points and in positions, then the “Arbitrary tie breaker” comes into play.

In case that after 3 games two players are equal in points and positions, but one player has only played 2 games and the other one has played 3 games, the player who played 3 games must be ranked higher.

The names of the players are in columns E40:E60.

Their points are in GU40:GU60.

In GV40:GV60 I have the best positions per player.

In GW40:GW60 the second best positions, … in HQ40:HQ60 the 22nd best position.

In HR40:HR60 I have the number of games played per player.

In HS40:HS60 the total of games played.

In HT40:HT60 the percentage of games played per player.

I wish that it is clear what I want and that you can help me.

Thank you very much in advance!

Gr. Anthoney.

Hi

ANTHONEY,Thanks for your comment. You requirement needs the Excel file. Please share your Excel file and repost your problem on our official

ExcelDemy Forum. Our experts will try to reach you out.Regards

Rafiul HasanTeam

ExcelDemy