How to rank based on two columns in Excel?
When working with Excel datasets that contain names and two different sets of scores (for example, test results, project grades, or sales data across multiple periods), you might sometimes need to rank the names based on a combination of these two scores. This requirement is common in scenarios where overall performance or combined achievements need to be evaluated, such as awarding scholarships, determining top sales representatives, or combining results from two segments of a competition. However, Excel does not provide a direct function to rank based on multiple columns; instead, you need to apply creative solutions to achieve the desired ranking.
Rank on two columns
One efficient way to handle the ranking of names based on two score columns is to use a custom formula that considers both sets of scores. Typically, the first score is treated as the primary criteria, and the second score is used as a tiebreaker. This approach is helpful when you want to maintain the original order for tied scores based on a secondary performance metric.
To apply this method, follow these steps:
1. Select a blank cell where you want the ranking results to appear. For example, choose cell D2.
2. Enter the following formula:
=RANK(B2,$B$2:$B$7)+SUMPRODUCT(--($B$2:$B$7=$B2),--(C2<$C$2:$C$7))This formula first calculates the rank based on the first score column. When there are ties (i.e., duplicate scores), it adjusts the rank according to the second score, ranking the tied entries based on the secondary value. The SUMPRODUCT part acts as a tiebreaker, counting instances where the first scores are the same, but the second score is lower.
3. Press Enter to confirm the formula.
4. Drag the fill handle down to apply the formula to other cells in column D for the rest of the data.
In this formula, B2 and C2 refer to the first data cells in the first and second score columns, respectively, and $B$2:$B$7, $C$2:$C$7 reference the ranges containing all the scores. Adjust these references if your data range is different.
This method works well for small to medium-sized datasets and when your tiebreaker is simply the next score column. However, if you need to handle larger datasets or require more complex ranking rules, consider the following alternative approaches.
VBA Code - Automate ranking based on two columns for larger datasets or customized ranking rules
If you are working with a large dataset or need to automate ranking based on customized logic (for instance, incorporating more conditions or applying the ranking to varying ranges), using VBA can be a practical solution. This approach is ideal if manual formula application becomes too cumbersome, or if you require batch processing across multiple sheets.
1. Click Developer Tools > Visual Basic. When the Microsoft Visual Basic for Applications window opens, click Insert > Module, and copy and paste the following code into the module:
Sub Rank_Two_Columns()
Dim lastRow As Long
Dim ws As Worksheet
Dim rngScore1 As Range, rngScore2 As Range, rngRank As Range
Dim i As Long, j As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row 'Assume scores are in columns B and C
Set rngScore1 = ws.Range("B2:B" & lastRow)
Set rngScore2 = ws.Range("C2:C" & lastRow)
Set rngRank = ws.Range("D2:D" & lastRow)
For i = 1 To rngScore1.Rows.Count
Dim rankCount As Long
rankCount = 1
For j = 1 To rngScore1.Rows.Count
If rngScore1.Cells(j, 1).Value > rngScore1.Cells(i, 1).Value Then
rankCount = rankCount + 1
ElseIf rngScore1.Cells(j, 1).Value = rngScore1.Cells(i, 1).Value Then
If rngScore2.Cells(j, 1).Value > rngScore2.Cells(i, 1).Value Then
rankCount = rankCount + 1
End If
End If
Next j
rngRank.Cells(i, 1).Value = rankCount
Next i
End SubThis code ranks rows based on the first score, and when there are ties, the second score is used as a tiebreaker. It assumes your first set of scores is in column B, second set in column C, and outputs ranking to column D (from D2 downwards). Adjust the column letters or ranges if your data is organized differently.
2. To execute the code, close the VBA editor, go back to Excel, press Alt + F8 to bring up the Macro dialog, select Rank_Two_Columns, and click Run. The rankings will appear in column D.
When running macros, ensure that macros are enabled in Excel, and always save your work beforehand as macros cannot be undone. For particularly large datasets, this VBA solution can complete the ranking process much faster than manually copying formulas.
If you encounter errors such as "Subscript out of range" or see that the rankings do not appear, check that your data contains no blank rows within the range and that the score columns are correctly referenced in the code.
Other Built-in Excel Methods - Use helper columns to combine the two scores and rank
For users who prefer not to use formulas with array logic or VBA, ranking can also be achieved using Excel’s built-in sorting tools with a helper column. This approach is particularly simple when both score columns consist of numerical values, and you wish to apply a weighted or concatenated sort logic.
Here’s how you can do it:
1. Insert a new column (for example, column D) to serve as a helper.
2. In the first row of the helper column (e.g., D2), enter a formula that uniquely ranks each row. You may concatenate the two scores, or apply weights if you want one score to count more than the other. For example, if the first score counts as 60%, and the second as 40%, enter:
=B2*0.6+C2*0.43. Press Enter and copy this formula down to apply to all rows.
4. Select all your data (including names and both score columns).
5. Go to the Data tab and choose Sort. In the Sort dialog box, set the helper column as the key for sorting, and choose to sort by Largest to Smallest.
6. Your data will now be ordered based on the combined ranking logic.
This method requires no advanced formulas or scripting and is user-friendly if you need to apply different weights to your ranking criteria. However, keep in mind that for concatenating numbers as text (e.g., combining scores of 90 and 88 into 9088), this will not work correctly if scores have varying digits. Weighted sums or scaled calculations are generally safer for generating unique and logical ranks.
If you need a ranking column, you can further apply the RANK function on the helper values. For example, in E2, enter:
=RANK(D2,$D$2:$D$7)Then drag to fill the ranking formula to all rows.
Note: Double-check that the calculation method in the helper column actually reflects your intended ranking rule. This approach is best suited for simple "overall score" ranking or when you want a fast, visual way to sort your data.
In summary, ranking data based on two columns in Excel can be achieved via formulas for dynamic and straightforward scenarios, VBA macros for large or customized tasks, and helper columns combined with sorting for simple weighted or concatenated ranking. Always verify your results by spot-checking ranks, especially after sorting or running scripts. If you receive inconsistent results, ensure there are no hidden or merged cells, blank rows, or text-formatted numbers in your data.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Relative Articels
- How to quickly rank a list by a specific value in Excel?
- How to easily rank list without ties in Excel?
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