Excel has three functions that can rank values. Unfortunately none of them provide a way to create a sequential list of ranking numbers when there are duplicates. Luckily we can adapt the RANK function to handle duplicates and create a sequential list of ranking numbers.

In the image below I have used Excel’s three ranking functions.

When there are duplicates none of the functions provide a sequential list of numbers that provide a complete ranking for the list.

The RANK and RANK.EQ duplicate the lowest ranking number and omit numbers. In this case 2 is listed twice but there is no 3.

The RANK.AVG averages the ranking numbers and displays the average. So the ranks 2 and 3 are averaged to 2.5 and are shown twice

To get a sequential list of ranking numbers we can modify the RANK function.

In the image below I have added a COUNTIF function to the RANK function to provide the sequential ranking numbers.

Note the COUNTIF range expands as it is copied down as the first cell reference is fixed with $ signs, and the second cell reference is relative.

You can use this sequential list of ranking numbers to extract a listing based on top five or top ten values when creating things like dashboards and reports.

This handles multiple duplicates – see image below.

The value 300 is repeated 4 times and the formula still works.