Often you may want to rank numbers in Excel and force each number to have a unique rank.

You can use the following formulas to do so:

**Method 1: Rank Numbers Uniquely in Ascending Order**

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

**Method 2: Rank Numbers Uniquely in Descending Order**

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

Both formulas will rank the numbers uniquely in the range **B2:B11**.

If two numbers are the same, then the number that appears first will receive the lower rank.

The following example shows how to use each method in practice.

**Example: How to Rank Numbers Uniquely 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 with ranks ranging between 1 and 10.

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

**C2 (Non-Unique Rank)**:- =RANK(B2,$B$2:$B$11)

**D2 (Rank Uniquely in Ascending Order)**:- =RANK(B2,$B$2:$B$11)+COUNTIF(B$2:B2,B2)-1

**E2 (Rank Uniquely in Descending Order)**:- =RANK(B2,$B$2:$B$11,1)+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: Non-Unique Rank**

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

**Method 2: Rank Unique Ascending**

This method assigned a rank of 1 to the highest value.

If two values were tied, it simply assigned a lower rank to the value that appeared first.

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

**Method 3: Rank Unique Descending**

This method assigned a rank of 1 to the lowest value.

If two values were tied, it simply assigned a lower rank to the value that appeared first.

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

**Additional Resources**

The following tutorials explain how to perform other common operations 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