How to Rank Numbers Uniquely in Excel

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:

Excel unique rank

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.

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