Table of Contents
To rank a list of values in Excel, select the list of values, then go to the “Data” tab in the Ribbon and click on the “Sort” button. This will open a window where you can specify the criteria for sorting the list. Select the “Smallest to Largest” or “Largest to Smallest” option depending on what order you want the list to be in, then click the “OK” button to apply the sorting to the list. The list will now be ranked in the order specified.
You can use the following basic syntax to rank a list of values in Excel using VBA:
Sub RankValues()
Dim i As Integer
For i = 2 To 11
Range("C" & i) = WorksheetFunction.Rank(Range("B" & i), Range("B2:B11"), 0)
Next i
End Sub
This particular example ranks the values in cells B2:B11 and outputs the ranks in cells C2:C11.
The last argument of of 0 specifies that we should rank the values in ascending order (the largest value receives a rank of 1, the second largest value receives a rank of 2, etc.).
To rank the values in descending order, simply change the 0 to 1.
The following example shows how to use this syntax in practice.
Example: How to Rank Values Using VBA
Suppose we have the following list of basketball players along with their points scored:
Suppose we would like to calculate the rank of each value in the points column.
We can create the following macro to do so:
Sub RankValues()
Dim i As Integer
For i = 2 To 11
Range("C" & i) = WorksheetFunction.Rank(Range("B" & i), Range("B2:B11"), 0)
Next i
End Sub
When we run this macro, we receive the following output:
The rank of each value in the points column is displayed in column C.
For example:
- Player H with 41 points has the highest points value, so they receive a rank of 1.
- Player C with 40 points has the second highest points value, so they receive a rank of 2.
To instead rank the values in the points column in descending order, we can change the last argument in the Rank method from 0 to 1:
Sub RankValues()
Dim i As Integer
For i = 2 To 11
Range("C" & i) = WorksheetFunction.Rank(Range("B" & i), Range("B2:B11"), 1)
Next i
End Sub
When we run this macro, we receive the following output:
The rank of each value in the points column is displayed in column C.
For example:
- Player I with 11 points has the lowest points value, so they receive a rank of 1.
- Player E with 13 points has the second lowest points value, so they receive a rank of 2.
And so on.
Note: You can find the complete documentation for the VBA Rank method .