Yes, Excel can rank numbers with the RANK.EQ function. If the scores are in B2:B5, enter =RANK.EQ(B2,$B$2:$B$5,0) in C2 and copy it down; for scores 95, 88, 88, and 72, the ranks are 1, 2, 2, and 4. The final argument makes the highest score rank first.
What a rank means in Excel
A rank assigns a position to a value relative to other values in a list. It is useful for comparing test results, sales totals, response times, survey scores, or other numeric measures. Excel ranks the values in a range; it does not have to rearrange the rows to calculate their positions.
By default, the highest number is rank 1. Equal values receive equal ranks, so the next position may be skipped. For example, if two people tie for second place, the next person is fourth, not third. This is often called competition ranking, and it is the behavior of RANK.EQ.
A rank is only meaningful when the values measure the same thing and use the same basis. Ranking monthly sales alongside annual sales, for instance, would not create a fair comparison. Check that the range contains the intended records and that larger or smaller values really do represent better performance.
Use RANK.EQ for a standard ranking
In Microsoft 365 for Windows, put the numbers you want to compare in one column and use a neighboring column for the rank. With scores 95, 88, 88, and 72 in B2:B5, put this formula in C2:
=RANK.EQ(B2,$B$2:$B$5,0)
The formula returns 1 for the first score, 2 for either 88, and 4 for 72. The zero requests descending order, which means the largest value gets rank 1. The dollar signs lock the comparison range when you copy the formula down, while B2 stays relative and changes to B3, B4, and B5 in the copied formulas.
To fill the remaining rows, select C2 and drag the small square at the lower-right corner of the cell down through C5. You can also copy C2, select C3:C5, and paste. Check that every copied formula still refers to the full range $B$2:$B$5; if the range shifts, the ranks can be wrong.
The third argument is optional. Leaving it out, or entering 0, ranks the largest number first. Entering any nonzero value, conventionally 1, ranks the smallest number first. The reference should normally be absolute when the formula is copied, but the value being ranked should remain a relative cell reference.
Rank from lowest to highest
Ascending rank is useful when low values are preferable, such as elapsed time, defect counts, or costs. With the same scores in B2:B5, enter this in C2 and copy down:
=RANK.EQ(B2,$B$2:$B$5,1)
For 95, 88, 88, and 72, the results are 4, 2, 2, and 1. The lowest score, 72, is first; the tied 88s share second; and 95 is fourth. The function changes the ranking direction, not the underlying data or the order of the rows.
Be clear about what “best” means before choosing the order argument. A low response time may be best, while a high satisfaction score may be best. If you reverse the order accidentally, Excel still calculates a valid ranking, but it answers the opposite question.
What happens when values tie
RANK.EQ gives tied values the same rank and leaves a gap after them. That is appropriate for many leaderboards: if two entries tie for first, both are first and the next is third. It does not arbitrarily pick one tied row as the winner.
If you want tied values to share the average of the positions they occupy, use RANK.AVG. With scores 95, 88, 88, and 72 in B2:B5, put this in C2 and copy it down:
=RANK.AVG(B2,$B$2:$B$5,0)
The two 88s each return 2.5, the average of positions two and three; 95 returns 1 and 72 returns 4. This option can be useful when you want ties to have a fractional rank rather than a gap in the ranking sequence. It does not break a tie or choose which tied record should appear first.
Sometimes a report needs a unique position for every row. One transparent way to break ties is to award the earlier row the better position. With the same values in B2:B5, enter this in C2 and copy down:
=RANK.EQ(B2,$B$2:$B$5,0)+COUNTIF($B$2:B2,B2)-1
This returns 1, 2, 3, and 4 for the example. For the second 88, the rank is 2 and its occurrence count so far is 1, so the formula returns 2. For the next 88, the rank is 2 and its occurrence count is 2, so it returns 3. This method uses row order as the tie-break rule; sort the data first or use a different rule if row order should not decide the outcome.
Rank within a group or category
A common next step is ranking each person against others in the same team, region, or product category rather than against the whole table. Suppose categories are in A2:A5 and scores are in B2:B5: Red has scores 95, 88, and 72, while Blue has 88. In C2, enter this formula and copy it down:
=1+COUNTIFS($A$2:$A$5,A2,$B$2:$B$5,">"&B2)
For the Red score of 95, no Red score is higher, so its rank is 1. Red’s 88 has one higher score in its category, so it ranks 2; Red’s 72 has two higher scores and ranks 3. Blue’s 88 ranks 1 because there are no higher Blue scores. This formula uses a strict greater-than comparison, so tied scores receive the same rank and the next rank can have a gap.
The category and score ranges are fixed with dollar signs, while A2 and B2 change as the formula is copied. If your table grows, consider converting it to an Excel table so new records are easier to include, or update the formula ranges. Always verify that the category labels are consistent: a trailing space or a spelling difference can make Excel treat records as separate groups.
Ranking is not the same as sorting
Ranking calculates positions while leaving records in place. Sorting rearranges rows according to a chosen column. If you need to preserve the original order and show each record’s position, add a rank formula. If you want the highest-scoring record at the top of the table, sort the table instead, or do both if the displayed rank is useful.
In Excel for Microsoft 365 on Windows, select a cell in the data and use Data > Sort. Choose the score column and select largest-to-smallest or smallest-to-largest. If the data has headers, confirm that Excel recognizes them so the heading is not sorted into the records. Sort the entire table, not just the score column, or names and other details may no longer match their original scores.
On Mac and Excel for the web, the same basic distinction applies: sorting changes row order, while a rank formula displays a calculated position. Ribbon labels and dialog layouts can differ slightly by platform, but RANK.EQ and RANK.AVG work in current Excel versions. Use a table or select the complete data range before sorting to keep related columns together.
Common ranking problems and checks
- The ranks change as you copy: Lock the full comparison range, as in
$B$2:$B$5. Keep the individual value reference relative so each row ranks its own score. - The rank direction is backward: Check the final argument. Zero or an omitted argument ranks largest first; 1 ranks smallest first.
- A value is missing from the ranking range: Expand the reference to include all records. If the value being ranked is not present in the reference, Excel can return
#N/A. - Numbers are treated unexpectedly: A value stored as text may not behave like a numeric score. Check the cell format and the underlying value, and convert genuine numeric text to numbers if needed.
- Blank cells or headers affect the result: Use a range containing the numeric data, not its heading. Blank cells are not scores; decide separately how blank records should appear in the report.
- The result skips a number: That is normal with
RANK.EQwhen values tie. UseRANK.AVGfor average tied positions, or choose an explicit tie-break rule if every row needs a distinct rank.
When formulas appear as text instead of results, check that the cell is not formatted as Text and that the entry begins with an equals sign. After changing a cell from Text to General, you may need to re-enter the formula. Also check that the formula uses commas between arguments; some regional settings use a different list separator, in which case Excel may expect semicolons.
Choosing the right ranking method
For most lists, start with RANK.EQ: it is straightforward, handles duplicates consistently, and clearly communicates that ties share a rank. Choose the order argument based on whether high or low values are preferable. Use RANK.AVG only when average positions are meaningful for your report.
Before sharing results, decide how ties should be interpreted, what to do with missing values, and whether comparisons should be overall or within categories. Then test a few rows by hand: identify the largest or smallest values, count how many values are above or below a sample, and compare that with the formula output. This quick check can reveal an incorrect range or ranking direction.
Frequently Asked Questions
Can Excel rank numbers automatically?
Yes. Enter a formula such as =RANK.EQ(B2,$B$2:$B$20,0) beside the first value and fill it down. Excel recalculates the ranks when values in the referenced range change.
Why does Excel skip a rank after a tie?RANK.EQ gives equal values the same position. If two values tie for second, the next value is fourth, because the tied records occupy positions two and three.
How do I rank the smallest number as number one?
Set the third argument of RANK.EQ to 1, as in =RANK.EQ(B2,$B$2:$B$20,1). The smallest value then receives rank 1.
Can I rank within each department?
Yes. Use a formula that counts only records with the same department and a higher score, such as the COUNTIFS example above. This calculates a separate ranking for each category.
Does ranking sort my data?
No. A rank formula returns a position but leaves each row where it is. Use Data > Sort to rearrange rows, and select the full table so related information stays together.
Related
- Calendar Schedule Template in Excel: How to Build One
- Free Calendar Excel Templates: How to Choose
- Calendar Layout Template for Excel: How to Make One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sum by Month: Formulas, Dates, and PivotTablesTo sum values by month in Excel, use SUMIFS with a start date for the month and an…
- Concatenate Excel Files: Combine Workbooks Step by StepTo concatenate Excel files—combine their data into one workbook—use Power Query in Excel for Microsoft 365 on Windows:…
- Add a Sort List in Excel: Create a Custom OrderTo add a custom sort list in Excel for Microsoft 365 on Windows, go to File > Options…
- Excel VLOOKUP, HLOOKUP, and XLOOKUP: Key DifferencesVLOOKUP searches down the first column of a table, HLOOKUP searches across its first row, and XLOOKUP searches…
- DAX Function in Excel: What It Does and How to Use ItDAX in Excel is a formula language for calculations in the workbook Data Model, used through Power Pivot…
- Extract Text in an Excel Cell: Formulas and Steps That WorkTo extract part of a cell’s text, use a formula such as TEXTBEFORE for text before a separator,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.