To rank numbers in Excel, use RANK.EQ. With the value to rank in B2 and comparison values in B2:B20, enter =RANK.EQ(B2,$B$2:$B$20,0) in C2: zero ranks the largest number as 1, while 1 ranks the smallest number as 1.
What ranking means in Excel
A rank shows where a number stands relative to other numbers in a list. If higher scores are better, the highest score is rank 1. If lower values are better, such as a shorter completion time, the lowest value can be rank 1 instead. Excel does not rearrange the list when you calculate ranks; it returns a number beside each item so you can compare results while keeping the original row order.
For example, imagine scores of 92, 85, 85, 70, and 60. In descending order, 92 is first, both 85s are tied for second, 70 is fourth, and 60 is fifth. The missing third place is a normal consequence of the tie: both identical values receive the same rank, and the next rank reflects the number of values ahead of it.
Enter a basic RANK.EQ formula
In Microsoft Excel for Microsoft 365 on Windows, put each score in a column and use a neighboring column for its rank. With scores in B2:B6 and the first rank formula going in C2, enter =RANK.EQ(B2,$B$2:$B$6,0). For the example scores 92, 85, 85, 70, and 60, the result in C2 is 1 because 92 is the largest value.
The function has three arguments: the number to rank, the range to compare it with, and an optional order setting. The third argument is 0 for descending order (largest first) or 1 for ascending order (smallest first). You can also omit the third argument; Excel treats an omitted order as zero, so the largest value ranks first. Including 0 or 1 explicitly makes the intended direction easier to see later.
After entering the formula in C2, copy it down the column using the fill handle or copy and paste. The first reference, B2, changes to B3, B4, and so on. The dollar signs keep the comparison range fixed at B2:B6. Without them, copying the formula would shift the comparison range and produce ranks based on a different set of cells in each row.
Choose ascending or descending order
The order argument changes what “rank 1” means; it does not change the numbers themselves. Use descending order for measures where more is better, such as sales, points, or test scores. Use ascending order for measures where less is better, such as elapsed time, defect count, or a price you want to rank from lowest to highest.
- Descending: with scores in B2:B6 and the formula in C2,
=RANK.EQ(B2,$B$2:$B$6,0)gives 92 rank 1 and 60 rank 5. - Ascending: with those same scores and cell layout,
=RANK.EQ(B2,$B$2:$B$6,1)gives 60 rank 1 and 92 rank 5.
Check the direction against the meaning of the data before filling a formula through a report. A ranking can calculate correctly and still answer the wrong question if the order is reversed. For example, ranking finishing times in descending order puts the slowest time first, which is usually not the desired interpretation.
Understand ties and RANK.AVG
RANK.EQ assigns equal numbers the same rank. With scores in B2:B6 of 92, 85, 85, 70, and 60, and the descending formula =RANK.EQ(B2,$B$2:$B$6,0) copied down, the ranks are 1, 2, 2, 4, and 5. The tie does not mean Excel has skipped a calculation: both 85s occupy the positions that would otherwise be second and third, so the next distinct score is fourth.
If you prefer tied values to receive the average of the positions they occupy, use RANK.AVG. With scores in B2:B6 and the formula in C2, =RANK.AVG(B2,$B$2:$B$6,0) returns 2.5 for either 85. The tied values occupy places 2 and 3, whose average is 2.5; the resulting ranks for the five example scores are 1, 2.5, 2.5, 4, and 5. This is useful when an average position is more meaningful than competition-style ranks.
Both functions are available in modern Excel. The older RANK function remains for compatibility with older workbooks, but Microsoft provides RANK.EQ and RANK.AVG to make the two tie-handling choices explicit. If you are sharing a workbook with someone using an older Excel release, check that release’s function support before relying on newer features.
Rank numbers within a category
Sometimes a number should be compared only with values in the same group—for example, ranking each salesperson against colleagues in the same region. A regular RANK.EQ across the entire score column will compare every row, regardless of category. For a descending rank within each group, a conditional count can calculate how many same-group values are greater than the current value, then add 1.
With regions in A2:A10, scores in B2:B10, and the group rank formula in C2, enter =COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,">"&B2)+1. If A2 contains North and its score is 85, the formula counts scores greater than 85 in North, not in other regions. If one North score is higher, the result is 2. Copy the formula down to rank each row within its own region.
This method gives equal scores the same competition rank and leaves gaps after ties, just like RANK.EQ. It is also a useful pattern when the comparison depends on more than one condition, since COUNTIFS can apply multiple criteria. Make sure the category and score ranges cover the same rows; mismatched range sizes can cause a formula error.
Keep rankings reliable as data changes
A fixed range such as $B$2:$B$20 is clear and works well when the list size is stable. If you add new rows below it, however, those rows are not automatically part of the comparison. You can expand the range when you add data, or format the dataset as an Excel table so formulas and ranges are easier to maintain as the table grows.
For example, suppose an Excel table is named Scores and its numeric column is named Points. With the current row’s value in the table’s Points column and the rank formula entered in another table column, use =RANK.EQ([@Points],Scores[Points],0). This structured-reference formula compares the current row with the table’s Points values and typically fills down the calculated column automatically. The table and column names must match the names in your workbook.
Another practical check is whether the cells contain real numbers. Values imported as text may not behave like numeric scores in calculations. If results look unexpected, select a sample cell and check whether Excel recognizes it as a number; correcting the source data is usually better than building a ranking formula around inconsistent text and numbers. Also remember that a rank formula includes the values in its specified comparison range even if some rows are hidden or filtered out.
Ranking is not the same as sorting
Ranking adds a position number to each row without changing the order of the data. Sorting rearranges rows according to a selected column. If you need to keep an original record order while showing each score’s position, use a rank formula. If you want the highest or lowest scores displayed together at the top, sort the table by the score column instead.
You can use both: add a rank column, then sort the entire table by that rank. Sort the whole table rather than just the rank cells, or the rank values may become separated from their names and other details. A rank column is also helpful when you want to filter or format particular positions, but tied values can mean more than one row has the same rank.
Common ranking problems
- Every row gets rank 1: check whether the formula’s comparison range accidentally contains only the current cell, or whether copied formulas have shifted the range. Use dollar signs around the full range.
- The ranking runs in the wrong direction: set the third argument to 0 for largest-first or 1 for smallest-first.
- The next rank skips a number: this is expected with
RANK.EQwhen values tie. UseRANK.AVGif average ranks are preferable. - New rows are not included: extend the comparison range or use a table-based reference that expands with the data.
- Ranks look inconsistent: verify that all compared cells contain numeric values and that the formula points to the intended range.
If the formula displays as text instead of calculating, the cell may be formatted as Text or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General, then re-enter the formula. If Excel displays a formula error, inspect the range endpoints and the commas and parentheses in the expression.
Frequently Asked Questions
How do I rank numbers in Excel from highest to lowest?
Use RANK.EQ with 0 as the order argument. With the value in B2 and the comparison range in B2:B20, enter =RANK.EQ(B2,$B$2:$B$20,0).
How do I rank numbers from lowest to highest?
Use 1 as the third argument. With the same cell layout, enter =RANK.EQ(B2,$B$2:$B$20,1); the smallest value receives rank 1.
Why does Excel give two values the same rank?RANK.EQ gives equal numbers equal ranks. The following distinct number may have a rank gap, such as ranks 2, 2, and 4, because two values occupy the second and third positions.
Can Excel rank values within each group?
Yes. Use COUNTIFS to count larger values that share the current row’s group, then add 1. The group-rank example above compares scores only within each region.
Does ranking sort my data?
No. A rank formula returns a position in a cell but leaves rows where they are. Use Excel’s sort controls if you want to rearrange the records by score or rank.
Related
- Excel Number Format for Leading Zeros: How It Works
- Excel Number Format for Leading Zeros: A Practical Guide
- Excel Number Format: How to Show Zero as a Dash in Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Microsoft 365 XLOOKUP Examples for Everyday Excel TasksIn Microsoft 365, XLOOKUP finds a value in one range and returns the corresponding value from another: use…
- Excel INDIRECT: Link to Another Tab with a FormulaTo refer to a cell on another worksheet using a sheet name stored in A1, enter =INDIRECT("'"&A1&"'!B2"). If…
- Excel FILTER Formula for Multiple CriteriaTo filter rows in Excel by multiple criteria, use FILTER with a condition for each test: multiply the…
- Excel Color Filter: How to Filter Rows by Cell ColorTo filter rows by color in Excel for Microsoft 365 on Windows, select a cell in your data,…
- Excel Greater Than or Equal To: Formula and ExamplesIn Excel, the greater-than-or-equal-to operator is >=. Put it between two values in a formula, such as =A1>=B1,…
- Excel MAX Function: Find the Highest Value in a RangeTo find the largest number in Excel, enter a formula such as =MAX(B2:B10) in an empty cell, replacing…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.