Excel Rank Formula for Scores: How to Rank Results

Formulas & functions · Updated November 18, 2026

To rank scores in Microsoft Excel for Microsoft 365, use =RANK.EQ(B2,$B$2:$B$6,0) when the score to rank is in B2 and all scores are in B2:B6. The final 0 ranks from highest to lowest, so the largest score is rank 1; use 1 instead to rank from lowest to highest.

The basic formula for ranking scores

RANK.EQ is the standard choice when you want a score’s position compared with a group of scores. The function takes three arguments: the number being ranked, the range containing the comparison values, and an optional order setting. The comparison range should include every score you want considered.

With the score to rank in B2 and the full set of scores in B2:B6, enter =RANK.EQ(B2,$B$2:$B$6,0) in C2. The dollar signs keep the comparison range fixed when you copy the formula down, while the relative reference B2 changes to B3, B4, and so on. In Microsoft 365 for Windows, press Enter, select the cell, and drag or double-click its fill handle to copy the formula to other rows. On Mac, the formula is the same; the fill handle works similarly.

The result is a position, not a rearrangement of the data. A rank of 1 means the score is at the top under the chosen order. The original score list remains where it is, which is useful when names, dates, or other details must stay aligned with each score.

Worked example: rank a list of scores

Suppose B2:B6 contains the scores 92, 85, 92, 76, and 88, in that order. Put a ranking formula in C2 and fill it through C6. With the score in B2 and the comparison range B2:B6, enter =RANK.EQ(B2,$B$2:$B$6,0). The results are 1, 3, 1, 5, and 4, respectively.

The two scores of 92 are tied for first. Excel gives both a rank of 1, and the next score, 88, receives rank 3. This is often called competition ranking because a tie uses the same position for each tied value and leaves a gap afterward. The 85 is fourth and the 76 is fifth. If the scores are in another column, substitute that column’s cell and range while keeping the same structure.

Check the chosen order before interpreting the result. If the highest score should win, use 0 or leave the optional order argument out. If the lowest score should come first, use 1. Including the order explicitly is usually clearer to someone reviewing the workbook later.

What each RANK.EQ argument means

The first argument is the single numeric score whose position you want. The second is the set of scores to compare it against. The third controls sort direction: 0 means descending order, with the largest number ranked first; 1 means ascending order, with the smallest ranked first. Excel treats any nonzero order value as ascending, but using 1 makes the intention plain.

For a score in B2 compared with B2:B6, =RANK.EQ(B2,$B$2:$B$6) gives the same descending result as explicitly supplying 0. The order argument is optional, and omitting it defaults to descending order. The score being ranked should be included in the comparison range; otherwise the result describes its position only among the values that are included.

Use an absolute reference for a comparison range that should not move as the formula is copied. In the example, $B$2:$B$6 stays fixed, whereas B2 changes by row. Without the dollar signs, copying the formula down could shift the comparison range and produce a different comparison set on each row.

How ties work: RANK.EQ and RANK.AVG

RANK.EQ assigns equal scores the same rank. It does not break a tie based on row order, name, or another column. The following rank is skipped, as in the example where two first-place scores are followed by rank 3. That behavior is appropriate for many scoreboards and exam summaries.

If tied scores should share the average of the positions they occupy, use RANK.AVG instead. With the score in B2 and scores in B2:B6, enter =RANK.AVG(B2,$B$2:$B$6,0). For the example values, the two scores of 92 each receive 1.5, because they occupy the first and second positions; 88 receives 3, 85 receives 4, and 76 receives 5. The function changes how ties are reported, not the order of the scores.

Choose the tie method to match the rules used for the results. For example, a competition may award equal scores equal ranks and skip the next place, while a report may prefer an average rank. Neither method creates a unique ordering among tied students. If a formal contest has a tie-break policy, apply that policy separately and make the rule visible rather than assuming Excel can infer it.

Rank scores within a group

A common follow-up is ranking each person only against people in the same class, team, or category. One way is to count how many scores in the same group are strictly higher, then add one. This produces competition ranking and gives tied scores the same rank.

With group names in A2:A7, scores in B2:B7, and the first result in C2, enter =COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,">"&B2)+1. Copy the formula down. If A2 is Blue and two Blue entries have higher scores than the score in B2, the result is 3. The group condition limits the comparison to rows whose label matches A2; the score condition counts only values above B2. Use consistent group labels and numeric scores so the comparison is meaningful.

This approach can be useful when a workbook contains several separate classes or divisions. It also makes the ranking rule explicit, but it is not a substitute for checking the data: inconsistent labels such as “Blue” and “Blue ” may be treated as different groups. For a small number of groups, another option is to filter the table to one group and rank only the visible group’s scores with a normal rank formula, though a formula designed for grouped ranking is easier to keep current.

Common problems and checks

If every row appears to have the same rank, check whether the formula’s score reference changed as you copied it. The cell being ranked should normally be relative, such as B2, while the full comparison range should be fixed, such as $B$2:$B$6. Also verify that the comparison range covers all intended rows and does not accidentally include a heading.

RANK.EQ ranks numeric values in its reference and ignores text and empty cells in that reference. However, ranking a blank score cell directly can behave like ranking a zero, which may give an unintended result. If blank score cells should stay blank, add a blank check. With scores in B2:B6 and the formula in C2, use =IF(B2="","",RANK.EQ(B2,$B$2:$B$6,0)) and fill down. This leaves the result empty when B2 is empty; it does not validate whether a nonblank entry is a legitimate score.

Numbers stored as text are another source of confusing results. A value that looks like 92 may not behave like the numeric value 92 if it was imported or entered with a leading apostrophe. Check the cell format and the underlying value, and convert text values to numbers when appropriate. Avoid fixing the issue by changing the rank order unless the intended rule really is lowest-first.

Decide whether zeros and incomplete records belong in the comparison set. A zero may be a genuine score, while an empty cell may mean the participant has not yet completed the test. Including a genuine zero in a descending ranking puts it below positive scores; treating an uncompleted score as zero may incorrectly place it as a completed result. Clear data rules make the formula’s output easier to interpret.

Choosing between ranking, sorting, and percentiles

Use a rank formula when you need a position beside each original record. Sorting is better when the goal is to display the full table from highest score to lowest, especially when names and other fields should move together. Sort the entire table, not just the score column, or records can become mismatched. Ranking and sorting answer related but different questions: one labels each score’s position, while the other changes the display order.

Use a percentile or percentage-based measure when a reader needs relative standing on a scale rather than an ordinal place. A rank tells you how many positions a score occupies in a particular list; it does not show how large the score gap is. A score ranked third could be just one point behind first or far behind it. For a class, competition, or assessment, use the method specified by the relevant rules and label the result clearly.

For very large or frequently changing lists, Excel tables can help keep formulas and data organized as records are added. Still verify that the comparison set expands as intended and that new records use the same score and group conventions. A rank is only as useful as the population it compares against.

Frequently Asked Questions

How do I rank the highest score as number 1?
Use RANK.EQ with order set to 0. For a score in B2 and a comparison range of B2:B20, the formula is =RANK.EQ(B2,$B$2:$B$20,0). Copy it down for the remaining scores.

How do I rank the lowest score as number 1?
Set the order argument to 1. With the score in B2 and scores in B2:B20, use =RANK.EQ(B2,$B$2:$B$20,1). This is useful when a smaller value is better, such as a time or number of errors.

Why does Excel skip a rank after a tie?
RANK.EQ gives tied values the same position. If two scores share rank 1, the next score receives rank 3 because two entries are ahead of it. Use RANK.AVG if the desired result is the average of the tied positions instead.

Can I rank scores in an older Excel version?
RANK.EQ and RANK.AVG are available in Excel 2010 and later, including Microsoft 365. Earlier Excel versions use the older RANK function, which remains available for compatibility. In current Excel, RANK.EQ is clearer when equal scores should share the same rank.

Does the rank formula change the order of my rows?
No. It returns a number in the formula cell and leaves the records in their original positions. Sort the whole data table if you want the rows displayed in score order.

Can tied scores receive separate ranks?
Not with RANK.EQ alone: it gives equal values equal ranks. To assign a unique sequence, you need a stated tie-break rule, such as a secondary score or row order. Make that rule explicit because it changes how equal scores are treated.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.