How to Rank Without Duplicating in Excel 2007

One common need in Excel is to rank and sort data. This can be problematic when the data you're ranking has duplicate values, which results in duplicate rankings. Making a ranking list without duplication requires combining the RANK() function with the COUNTIF() function.

Couple using laptop
credit: Creatas Images/Creatas/Getty Images

Creating the Formula

Step

Start Excel and open up the data range you want to use. For this example, we're assuming that the data values to be ranked are in column A, starting in column A2.

Step

Enter the following formula in cell B2: =RANK(A2,$A$2:$A$10)+COUNTIF($A$2:A2,A2)-1. This formula ranks the data, using the value in A2 as the seed value, going through the ranks from top to bottom. The COUNTIF in the second part of the formula increments the rank number by 1, EXCEPT if the rank value is the same.

Step

Copy the formula in cell B2 through the entire range of cells from B3 to B10.