If names are in A2:A20 and their scores are in B2:B20, enter =RANK.EQ(B2,$B$2:$B$20,0) in C2 and fill it down. Excel then assigns rank 1 to the highest score; use 1 as the final argument instead of 0 to give rank 1 to the lowest score.
What ranking names in Excel means
Excel ranks numbers, not words. To rank names, you pair each name with a numeric value—such as sales, test scores, points, or completion times—and calculate each value’s position relative to the others. The rank is then shown beside the corresponding name. This distinction matters: Excel cannot use RANK.EQ to decide that one name is “first” alphabetically.
For example, a sales list might have names in one column and sales totals in another. A rank formula evaluates the sales total on each row against the full sales range, returning 1 for the top total, 2 for the next position, and so on. The names remain attached to their original rows until you sort the table.
Before ranking, decide what “best” means. For sales or exam scores, a larger number is often better, so rank in descending order. For race times or costs, a smaller number may be better, so rank in ascending order. Ranking answers a specific question about order; it does not by itself explain why a value is high or low.
Prepare the name and score columns
Keep one record per row, with a name or other identifier in one column and the number to rank in another. Add a heading to each column, such as Name, Score, and Rank. A unique identifier can help if two people share a name, while a date or category column can help you check which records belong in the comparison.
For a simple list, use a layout like this:
- Column A: names, starting in A2.
- Column B: numeric values, starting in B2.
- Column C: rank results, starting in C2.
Make sure the values you intend to compare are numbers. Values that look numeric but are stored as text may not behave as expected. Also check that the rank formula refers to the complete comparison range, not just the first few rows. In the example formula, dollar signs lock the range B2:B20 so it stays fixed when the formula is copied down, while B2 changes to B3, B4, and so forth.
How RANK.EQ assigns positions
RANK.EQ is the usual choice when you want equal values to receive the same rank. Its syntax is RANK.EQ(number, ref, [order]): number is the value to rank, ref is the comparison range, and the optional order determines whether the ranking runs from largest to smallest or smallest to largest. An order of 0, or leaving that argument out, ranks the largest value as 1. An order of 1 ranks the smallest value as 1.
With names in A2:A6 and scores in B2:B6, suppose the scores are 92, 88, 92, 75, and 88. In C2, enter =RANK.EQ(B2,$B$2:$B$6,0) and fill it through C6. The resulting ranks are 1, 3, 1, 5, and 3. Both scores of 92 share rank 1, and the next score is rank 3. That skipped rank is a normal consequence of tied positions, not a calculation error.
For a lower-is-better measure, such as elapsed time, use the same layout and change the final argument: =RANK.EQ(B2,$B$2:$B$6,1). The smallest time receives rank 1. A common source of confusion is using the wrong direction: Excel is following the order argument, even if the result seems backward for the question you are asking.
Choose how ties should appear
There is no single tie rule that suits every report. RANK.EQ uses competition ranking: tied values share a rank and later positions are skipped. In the example above, the two 92s take first place, so the next distinct score is third. This convention is common in leaderboards and results where tied participants occupy the same position.
If you want ties to receive the average of the positions they occupy, use RANK.AVG. With scores in B2:B6 as in the example, enter =RANK.AVG(B2,$B$2:$B$6,0) in C2 and fill down. The two 92s each receive 1.5, the two 88s each receive 3.5, and 75 receives 5. The average reflects the two places each tied value would cover if ordered separately.
Sometimes a report needs a unique sequence from 1 through the number of records, even when values tie. One simple rule is to give the earlier row the better position among equal scores. With names in A2:A6 and scores in B2:B6, enter =RANK.EQ(B2,$B$2:$B$6,0)+COUNTIF($B$2:B2,B2)-1 in C2 and fill down. For the sample scores, this returns 1, 3, 2, 5, and 4: equal values are ordered by their appearance in the source list.
That last method is a tie-break rule, not an indication that the tied scores are actually different. If fairness or reproducibility matters, document the chosen tie-breaker—for example, earlier entry date or a secondary score—instead of letting row order decide silently.
Sort the names by rank
A rank formula does not rearrange the rows. It calculates a result beside each original record, which is useful when you need to preserve the source order. To display the highest-ranked names first, sort the whole table, not just the Rank column; sorting only one column can detach the names from their scores.
For a standard table, select a cell in the data and use Data > Sort. Choose the rank column and sort Smallest to Largest so rank 1 appears at the top. If you want to rank directly by scores, sort the score column Largest to Smallest for a high-is-best measure, or Smallest to Largest for a low-is-best measure. Confirm that the sort applies to the full data range when Excel asks.
In Microsoft 365, a dynamic formula can display a sorted copy without changing the source rows. With names in A2:A6, scores in B2:B6, and ranks in C2:C6, enter =SORT(A2:C6,3,1) in an empty area. It sorts the three-column range by its third column—Rank—in ascending order. Leave enough blank cells below and beside the formula for the results to spill. If you used a tie rule, that rule determines the order of tied records.
Rank within a group or category
A single overall ranking may be misleading when records belong to different teams, regions, classes, or product categories. In that case, compare each value only with other values in the same group. In Microsoft 365, FILTER can provide the matching group’s scores as the reference range for RANK.EQ.
For example, assume names are in A2:A20, scores in B2:B20, and group labels in C2:C20. In D2, enter =RANK.EQ(B2,FILTER($B$2:$B$20,$C$2:$C$20=C2),0) and fill down. Each row is ranked against scores whose group label matches C2 on that row. FILTER is available in Microsoft 365 and Excel 2021 or later; the formula can return an error if no rows match, so check for spelling differences or extra spaces in group labels if that happens.
Before choosing a group ranking, consider whether the groups are genuinely comparable and whether every record should be included. A blank group label is still a value that may match other blank labels. If blank groups should not be ranked, exclude those records from the report or add a clear blank-handling rule rather than treating them as an ordinary category by accident.
Common ranking problems and checks
- Every row shows the same rank: Check whether the formula’s number reference changes by row and whether the comparison range is correctly fixed with dollar signs.
- The order seems reversed: Review the final order argument. Use 0 for largest-first and 1 for smallest-first.
- Some records are missing from the ranking: Confirm that the referenced range reaches the last row and that the scores are numeric rather than text.
- Blank rows show unwanted ranks: If names are in A2:A20 and scores are in B2:B20, enter
=IF(B2="","",RANK.EQ(B2,$B$2:$B$20,0))in C2 and fill down. The formula leaves the rank cell blank when B is blank. - Names appear separated from values after sorting: Undo the sort if possible, then sort the complete table rather than a single column.
RANK.EQ compares a value with the values in its reference range; it does not filter out records based on a separate condition unless the reference is constructed to do so. It also does not resolve duplicate names, determine whether a score is valid, or decide how missing data should be treated. Those are data-cleaning and reporting choices. For a reliable result, verify a few ranks manually—especially the highest, lowest, and tied values—and make sure the formula range matches the records described in the report.
A practical way to choose a ranking method
Use this decision process before sharing a ranked list:
- Identify the value being compared and decide whether high or low should receive rank 1.
- Choose a tie convention: shared ranks with gaps (RANK.EQ), average ranks (RANK.AVG), or a documented tie-break rule for unique positions.
- Decide whether the comparison is overall or limited to a group, and confirm the source range covers exactly those records.
- Keep each name, score, and rank together when sorting or presenting the results.
- Check the output against the original values and explain the ranking direction and tie policy in the worksheet.
This makes a ranking easier to interpret later, especially when the underlying scores change or another person updates the workbook. A rank is relative to its comparison group: adding a new record can change existing ranks even if none of their scores changed.
Frequently Asked Questions
How do I rank names alphabetically in Excel?
RANK.EQ ranks numeric values, not text names. To put names in alphabetical order, select the name column or a cell in the full table and choose Data > Sort A to Z. Sort the entire table if names have related scores or other details.
Why does Excel skip a rank after a tie?
RANK.EQ gives equal values the same position, so the next position is skipped. If two records tie for first, the next value is third. That is competition ranking; use RANK.AVG if you want tied values to share the average of their occupied positions.
How do I rank the lowest score as number 1?
Use 1 for RANK.EQ’s order argument. With the values to compare in B2:B20, a row’s formula can be =RANK.EQ(B2,$B$2:$B$20,1). This is appropriate when smaller values represent better results, such as shorter times.
Can Excel rank names without a score?
Not with RANK.EQ, because that function needs numeric values to compare. If you only need an alphabetical list, sort the names. If you mean a custom order, assign each name a numeric priority or use a separate lookup table that defines the desired order.
Does a rank formula sort the list automatically?
No. The rank appears in a cell, but the source rows stay in place. Use Data > Sort on the complete table to rearrange it, or use SORT in Microsoft 365 to create a sorted display elsewhere in the sheet.
Related
- Excel: How to Shade Every Other Row
- Microsoft Excel Snowball Debt Payoff Template Setup
- Microsoft Excel Redo Shortcut: How to Restore an Action
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIFS Excel: Sum Values That Meet Multiple CriteriaSUMIFS is Excel’s conditional-sum function: it adds values only when all the conditions you specify are met. With…
- Excel INDEX and MATCH: Find Values in Large WorksheetsIn Excel, INDEX and MATCH work together to find a value in one range and return the corresponding…
- Excel SUMIF After a Date: Formula and ExamplesTo sum values for dates strictly after a cutoff in Excel, use =SUMIF(A2:A100,">"&DATE(2024,1,1),B2:B100), where column A contains dates…
- Excel Cell Range Average: Formula and ExamplesTo average a range in Excel, use AVERAGE: if A2:A5 contain 10, 20, a blank cell, and 0,…
- Excel Filter Shortcut: Turn Filters On and Use ThemIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for the current…
- Excel Text Comparison: Formulas, Case, Spaces, and ErrorsWith the text values in A2 and B2, compare them in Excel using =A2=B2 for a case-insensitive check…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.