Yes. In Excel for Microsoft 365 on Windows, use RANK.EQ to assign a rank to a value, or RANK.AVG to give tied values their average rank. For example, a descending rank puts the largest number at 1; sorting is a separate action that rearranges rows into rank order.
How ranking works in Excel
A rank tells you where a value stands relative to the other values in a range. It does not change the order of the data. Excel compares the selected value with the reference range and returns a position based on whether you want the largest or smallest value to come first.
Ranking is useful for exam scores, sales totals, response times, product ratings, and other numeric measures. Before choosing a formula, decide what “best” means for the data. For a score where higher is better, rank largest to smallest. For a measure such as time taken or defect count where lower is better, rank smallest to largest.
- Descending order: the highest value receives rank 1.
- Ascending order: the lowest value receives rank 1.
- Ties: equal values can share a rank, with the next rank either skipped or averaged depending on the function.
Excel includes rank functions for numeric values. They do not create a meaningful rank for text labels such as names or departments; use a numeric measure associated with those labels.
Use RANK.EQ for a standard rank
RANK.EQ is the usual choice when tied values should share the same rank. Its arguments are the number to rank, the range to compare it against, and an optional order setting. Use 0, or leave the order argument out, for descending order; use 1 for ascending order.
With scores in B2:B6 of 92, 85, 92, 70, and 81, enter this in C2 and fill it down through C6. The formula keeps the comparison range fixed while the value being ranked changes by row:
=RANK.EQ(B2,$B$2:$B$6,0)
The results are 1, 3, 1, 5, and 4. Both scores of 92 receive rank 1, so rank 2 is skipped. The value 85 is rank 3, 81 is rank 4, and 70 is rank 5. The dollar signs make the reference to the full comparison range absolute, so it does not shift when the formula is copied.
To rank the same scores from smallest to largest, with the smallest at 1, use this instead in C2 and fill down:
=RANK.EQ(B2,$B$2:$B$6,1)
The resulting ranks are 5, 3, 5, 1, and 2. This can be useful when a smaller value represents a better result, such as a shorter completion time.
Choose between RANK.EQ and RANK.AVG
The key difference is how the functions treat ties. RANK.EQ returns the same rank for equal values and leaves a gap after the tied positions. RANK.AVG assigns each tied value the average of the ranks those values would otherwise occupy. Both functions can rank in ascending or descending order.
Using the scores in B2:B6 from the previous example, put this formula in C2 and fill it down if you want average ranks:
=RANK.AVG(B2,$B$2:$B$6,0)
The two scores of 92 each receive 1.5 because they occupy first and second place; 85 receives 3, 81 receives 4, and 70 receives 5. Average ranks are often appropriate for statistical summaries, while equal ranks with skipped positions are familiar in competition tables. Choose the treatment that suits the rules of your report, and label the result if readers might interpret ties differently.
RANK.EQ and RANK.AVG are available in modern Excel, including Microsoft 365. The older RANK function remains for compatibility with older workbooks. For a new workbook, the explicit function names make the intended treatment of ties clearer.
Rank values within a group
A common follow-up is ranking each person against others in the same team, region, class, or category rather than against the whole worksheet. If column A contains a group and column B contains a score, COUNTIFS can count how many scores in that group are strictly higher than the current row. Add 1 to convert that count into a descending rank.
With group names in A2:A7 and numeric scores in B2:B7, enter this in C2 and fill down:
=COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,">"&B2)+1
For example, if the East group has scores of 120, 90, and 120, each score of 120 gets rank 1 and 90 gets rank 3. If West has 150, 100, and 70, those scores rank 1, 2, and 3 within West. Because the formula counts only strictly higher scores, equal scores receive the same rank and the next rank is skipped. The fixed ranges keep the group and score lists intact as the formula is copied; the row references A2 and B2 update.
This approach assumes the group labels and scores line up row by row and that the scores are numeric. If you need ascending group ranks, count values in the same group that are lower instead, changing the comparison criterion from greater than to less than.
Sort rows into rank order
Ranking and sorting answer different questions. A rank formula adds a number beside each original record but leaves the records where they are. Sorting rearranges records, which can make a leaderboard easier to read but may affect the layout and any formulas that depend on row positions. When a record has several columns, sort the complete table so names remain attached to the right scores.
For a one-time sort in Excel for Microsoft 365 on Windows, select a cell in the data table and choose Data > Sort. Choose the score column under Sort by and select Largest to Smallest or Smallest to Largest. If Excel asks whether to expand the selection, include the adjacent columns that belong to each record. Sorting only the score column can mismatch scores and names.
For a separate, formula-driven sorted result, suppose names are in A2:A6 and their scores are in B2:B6. Enter this formula in D2:
=SORTBY(A2:B6,B2:B6,-1)
It returns the two-column records sorted from highest score to lowest, with equal scores in the order they occur in the source range. The result spills into the cells below and to the right, so those cells must be empty. Use 1 as the final argument for ascending order. SORTBY is available in Microsoft 365 and Excel 2021 or later; older Excel versions may require the Data sort command or a different approach. On Excel for the web, dynamic-array formulas such as SORTBY are supported, though ribbon placement can differ slightly from Windows desktop Excel.
Highlight top-ranked values without reordering
If you want to call attention to the best values while keeping the data in place, conditional formatting can highlight them. Select the score cells, then choose Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items. Change 10 to the number of values you want to highlight, select a format, and confirm. The rule formats cells; it does not insert rank numbers or rearrange rows.
For a full leaderboard, use a rank formula in one column and conditional formatting or sorting as a separate presentation choice. That separation is helpful when the original row order carries meaning, such as dates or transaction sequence. If the table changes frequently, a formula-based ranking or a dynamic sorted output can update as values change, whereas a manual sort must be applied again after edits.
Common ranking problems and checks
- The largest value is rank 1 when you expected the smallest. Check the order argument: 0 means descending, and 1 means ascending.
- Ranks are skipped after a tie. That is standard for
RANK.EQ. UseRANK.AVGif tied values should receive average positions. - Copied formulas show unexpected ranges. Lock the comparison range with dollar signs, as in
$B$2:$B$6, while leaving the current-row reference relative. - A value does not rank as expected. Check that it is a real number rather than a number stored as text, and check for blanks or unintended entries in the comparison range. Non-numeric entries are not ranked as numeric values.
- Only one column moved during sorting. Undo the sort if possible and sort the entire table, not just the score cells, to preserve each record.
Also confirm that the comparison range includes exactly the records you intend to compare. Including a total row, old data, or a score from another category can change every rank. If new rows are added often, converting the range to an Excel table can make it easier to manage the data range, though you should still verify that formulas and sorting cover the intended records.
Frequently Asked Questions
What is the formula to rank numbers in Excel?
Use =RANK.EQ(B2,$B$2:$B$20,0) to rank the value in B2 against B2:B20 from largest to smallest. Adjust the cell references to fit your sheet and copy the formula down for other rows.
How do I make the lowest number rank first?
Set the third argument of RANK.EQ or RANK.AVG to 1. For example, 1 makes the smallest value rank 1; 0 makes the largest value rank 1.
Can Excel give duplicate values the same rank?
Yes. RANK.EQ gives equal values the same rank, then skips the positions they occupy. RANK.AVG also gives them the same rank, but uses the average of those positions.
How do I rank scores separately by category?
Use a group-aware formula such as COUNTIFS to count higher scores only among rows with the same category. Keep the category range, score range, and current row references aligned, then fill the formula down.
Does ranking sort the data automatically?
No. A rank formula returns a number in a cell but keeps rows in their existing positions. Use Data > Sort to rearrange a table, or use SORTBY in supported Excel versions to produce a separate sorted result.
Related
- Excel Borders: How to Add, Change, and Remove Them
- Excel Border Shortcuts: Apply, Change, and Remove Borders
- Bluebeam to Excel: Export Markups and Quantities
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF With Dates: Criteria, Ranges, and ExamplesTo count dates in Excel, use COUNTIF with a date value for an exact match, or join a…
- LAMBDA in Excel: A Practical Guide to Custom FunctionsYes—Excel’s LAMBDA function lets you create reusable custom functions without writing VBA. For a quick test, with no…
- Excel Date Subtraction: Formulas, Days, and WorkdaysTo subtract one date from another in Excel, subtract the earlier date cell from the later one: if…
- Add a Formula in Excel to Calculate DatesTo calculate a date in Excel, enter a starting date in one cell and use a formula such…
- IFERROR in Excel: Leave Blank When a Formula ErrorsTo make an Excel formula show a blank instead of an error, wrap it in IFERROR and use…
- Count Same Values in Excel with COUNTIFTo count how many times a value appears in Excel, use COUNTIF: if the values are in A2:A20…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.