How to rank data in reverse order in Excel?
In Excel, using the typical =RANK(cell, range) formula will rank your data so that the largest value receives a rank of1, while the smallest value is assigned the lowest rank number. However, there are situations where you may need to reverse this logic, giving the smallest value the top rank and the largest the last rank, as illustrated in the screenshot below. This can be particularly useful when you are analyzing data where lower values represent better performance, such as race times, costs, or error counts.
Rank data in reverse order | VBA Code: Assign reverse ranks automatically
Rank data in reverse order
To perform a reverse ranking in Excel, you can slightly modify the standard RANK formula by specifying an additional argument. This approach allows you to easily rank data from smallest to largest, rather than the default order.
Select a blank cell where you want the reverse rank result to appear. Assuming your data range is in cells B2 through B10, enter the following formula in the first cell adjacent to your data (for example, in cell C2):
=RANK(B2,$B$2:$B$10,1)After inputting the formula, press Enter. To apply the formula to the other cells in your column, use the fill handle: position your mouse over the lower right corner of the cell where you entered the formula (C2), and when the cursor turns into a plus sign, drag it down to fill the remaining cells in the range next to your data (down to C10 in this example). 
If your data range is different, simply adjust the cell references within the formula to match your actual data. Remember, if you have duplicate values, the RANK function will assign the same rank to those values, and subsequent ranks will be skipped (for example, if there are two items tied for second, the next rank will be four).
Reverse ranking is especially valuable when you want to highlight the lowest values or fastest times, and it works well on datasets where ties are either acceptable or expected.
VBA Code: Assign reverse ranks automatically
For larger datasets or scenarios where your data frequently updates and you require a more automated solution, you can use VBA code to assign reverse ranks quickly and efficiently. This method is ideal for users comfortable with small amounts of programming and aiming to eliminate repetitive manual steps.
1. Open the VBA editor by clicking Developer Tools > Visual Basic. In the Visual Basic for Applications window, click Insert > Module and paste the following code into the Module pane:
Sub AssignReverseRank()
Dim dataRange As Range
Dim outputRange As Range
Dim i As Long
Dim j As Long
Dim temp As Variant
Dim arr As Variant
Dim sortedArr As Variant
Dim rankArr() As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set dataRange = Application.InputBox("Select the data range to rank (single column):", xTitleId, "", Type:=8)
If dataRange Is Nothing Then Exit Sub
Set outputRange = Application.InputBox("Select the first cell for output ranks:", xTitleId, "", Type:=8)
If outputRange Is Nothing Then Exit Sub
arr = dataRange.Value
ReDim rankArr(1 To UBound(arr, 1), 1 To 1)
sortedArr = arr
' Sort the array in ascending order
For i = 1 To UBound(sortedArr, 1) - 1
For j = i + 1 To UBound(sortedArr, 1)
If sortedArr(i, 1) > sortedArr(j, 1) Then
temp = sortedArr(i, 1)
sortedArr(i, 1) = sortedArr(j, 1)
sortedArr(j, 1) = temp
End If
Next j
Next i
' Assign reverse ranks
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(sortedArr, 1)
If arr(i, 1) = sortedArr(j, 1) Then
rankArr(i, 1) = j
Exit For
End If
Next j
Next i
outputRange.Resize(UBound(rankArr, 1), 1).Value = rankArr
End Sub2. To run the macro, click the 
Run button or press F5. You will be prompted to select your data range (choose a single column that you want to rank in reverse) and then select the first cell where you want the ranking results to be output. Click OK after each selection.
This macro sorts your data in ascending order and assigns reverse ranks accordingly, placing the smallest value as rank 1. It's especially useful for ranking thousands of entries in one go, or for automating ranking in reports that refresh regularly.
When using this macro, ensure your data is contiguous and contains only numeric values. Any non-numeric or blank cells may result in incorrect output, so it’s best to clean your data first. Also, saving your workbook before running macros is a good precaution to prevent unintended changes.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in