There are three ways to handle ties when ranking a list of values in Excel:

**Method 1: Assign Highest Rank to Equal Values**

=RANK(B2,$B$2:$B$11)

This particular formula will assign the same rank to equal values in the range **B2:B11**.

For example, if two values are both tied for the highest value in the list then both values will receive a rank of **1**.

**Method 2: Assign Average Rank to Equal Values**

=RANK.AVG(B2,$B$2:$B$11)

This particular formula will assign the average rank to equal values in the range **B2:B11**.

For example, if two values are both tied for the highest value in the list then both values will receive a rank of **1.5** since the average of their rankings would be: (1 + 2) /2 = **1.5**.

**Method 3: Assign Highest Rank to Value that Appears First**

=RANK(B2,$B$2:$B$11)+COUNTIF(B$2:B2,B2)-1

This particular formula will assign the highest rank to the value that appears first between two equal values in the range **B2:B11**.

For example, if two values are both tied for the highest value in the list then the value that appears first will receive a rank of **1** and the value that appears second will receive a value of **2**.

The following example shows how to use each of these methods in practice.

**Example: How to Rank Values with Ties in Excel**

Suppose we have the following dataset in Excel that shows the exam scores received by various students in some class:

Suppose we would like to rank the exam scores of each student, assigning a value of 1 to the highest score and 10 to the lowest score.

We can type the following formulas into cells **C2**, **D2** and **E2** to apply different ranking methods:

**C2**: =RANK(B2,$B$2:$B$11)**D2**: =RANK.AVG(B2, $B$2:$B$11)**E2**: =RANK(B2,$B$2:$B$11)+COUNTIF(B$2:B2,B2)-1

We can then click and drag these formulas down to each remaining cell in each column:

Notice that there are two values that are tied for the highest score: Doug and Eric both received an exam score of 95.

Here is how each ranking method handled this tie:

**Method 1: RANK**

This method simply assigned a rank of **1** to both of these highest values.

**Method 2: RANK.AVG**

This method assigned a rank of **1.5** to both of these highest values, which represented the average rank if each of these values received their own individual rank.

For example, if Doug received a rank of **1** and Eric received a rank of **2** then the average between these two would be **1.5**.

**Method 3: RANK + COUNTIF**

This method assigned a rank of **1** to the first highest value that occurred and a **2** to the highest value that occurred next.

Since Doug appeared first in the dataset he received a rank of **1** and Eric then received a rank of **2**.

When ranking values in your own dataset, feel free to choose whichever method you prefer best.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

How to Calculate Percentile Rank in Excel

How to Rank Values by Group in Excel

How to Rank Items by Multiple Criteria in Excel