Excel Rank Formula: Rank a Number List Highest to Lowest

Formulas & functions · Updated November 18, 2026

To rank numbers from highest to lowest in Excel, use RANK.EQ with the order argument set to 0. With scores in B2:B6, enter this in C2: =RANK.EQ(B2,$B$2:$B$6,0), then fill it down; the largest score receives rank 1.

What the Excel rank formula does

RANK.EQ returns the position of a number within a group of numbers. Its optional third argument controls the direction of the ranking. Set it to 0, or leave it out, to rank in descending order: largest to smallest. Set it to a nonzero value, commonly 1, to rank in ascending order: smallest to largest.

The function returns a rank; it does not rearrange the values in the worksheet. If the largest value is 92, its descending rank is 1, while a lower value might rank 2 or 3. The rank is based on the comparison set you give Excel, so the range should include the records you want compared and exclude unrelated numbers.

In current Microsoft 365 Excel, RANK.EQ is the clearest choice when tied values should receive the same rank. It is also available in Excel 2010 and later. The older RANK function remains for compatibility with older workbooks, but new formulas generally use RANK.EQ or RANK.AVG.

Formula syntax and the order argument

The function’s syntax is RANK.EQ(number,ref,[order]). The number is the value whose position you want. The reference is the set of values used for comparison. The square brackets in the syntax indicate that order is optional; they are not typed into the formula.

  • Order 0 or omitted: largest value ranks 1, then values descend.
  • Order 1 or another nonzero value: smallest value ranks 1, then values ascend.
  • Fixed reference: dollar signs in $B$2:$B$6 keep the comparison range unchanged when the formula is copied.

Although omitting the third argument also means descending order, writing 0 makes the intended direction obvious to someone reading the formula later. A frequent source of confusion is treating 1 as the setting for highest-to-lowest. It is the opposite: 1 ranks from lowest to highest.

Worked example: ranking scores

Suppose the scores in B2:B6 are 92, 85, 92, 70, and 60. With those scores in B2:B6 and the formula entered in C2, use =RANK.EQ(B2,$B$2:$B$6,0) and fill it down through C6. The resulting ranks are 1, 3, 1, 4, and 5.

The two scores of 92 tie for first place. Excel gives both a rank of 1, then leaves rank 2 unused: 85 is third, not second. The rest of the values follow in order. This is often called competition ranking, as it matches a familiar result such as two people sharing first place and the next person receiving third.

Here is the example laid out as a small table:

Row Score Rank, highest first
2 92 1
3 85 3
4 92 1
5 70 4
6 60 5

The ranking is relative to the values included in the reference. If you add a score of 100 to the range, the existing scores’ ranks change because they are now being compared with a new highest value.

Copying the formula down a column

For a list with many rows, enter the formula once beside the first value and copy it down. The reference range needs absolute references, as in $B$2:$B$6, so it continues to compare every score against the same complete list. The first argument should remain relative, as in B2, so it changes to B3, B4, and so on for each row.

In Excel for Microsoft 365 on Windows, select the formula cell and drag its fill handle—the small square at the lower-right corner—down the column. You can also select the formula cell and the destination cells, then press Ctrl+D to fill down. On a Mac, the fill-down shortcut is Control+D; the Command key is not used for this Excel shortcut. If you add records later, update the fixed range or use a table so new rows are included in the comparison.

For a simple one-time list, a fixed range is easy to inspect. For a list that grows regularly, convert the data to an Excel table with Insert > Table. Table columns expand as records are added, which can make formulas and references easier to maintain. Check that the comparison set really includes the new records, since a rank formula cannot account for values outside its reference.

How ties affect ranks

RANK.EQ gives equal values equal ranks and skips the positions that those ties occupy. That behavior is right for many leaderboards, test scores, and sales rankings, but it is not the only useful convention. If tied values should receive the average of their occupied positions, use RANK.AVG.

With the same scores in B2:B6, enter this in C2 and fill it down to calculate average ranks: =RANK.AVG(B2,$B$2:$B$6,0). Each score of 92 receives rank 1.5, the average of first and second place; 85 receives rank 3. This method does not make tied records distinct. It changes the rank assigned to the tie, rather than breaking it.

A tie-break rule requires an additional piece of information, such as an earlier date, a secondary score, or a name-based rule. Decide and document that rule before trying to make every rank unique. Otherwise, a formula may produce an orderly-looking list that represents an arbitrary tie-break rather than a meaningful one.

Ranking is different from sorting

A rank column labels each record with its position but leaves the original rows in place. Sorting physically changes the row order. Use a rank formula if you need to keep the source data in its existing order, compare positions beside names, or make ranks update when scores change. Use sorting when the main goal is to display the records from highest score to lowest.

To sort a score list directly in Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > Sort Largest to Smallest. If each score belongs to a person or item, sort the full table or the complete related data range—not just the score column—so names and other fields stay with the correct records. Excel may ask whether to expand the selection; choose the option that keeps each row together.

To create a separate, dynamically sorted copy in Excel for Microsoft 365, assume names are in A2:A6 and scores are in B2:B6. Enter this formula in D2: =SORT(A2:B6,2,-1). It sorts the two-column array by its second column in descending order, and the results spill into nearby cells. SORT is available in Microsoft 365 and Excel 2021 or later; it is not the same as assigning ranks, and it does not change the source rows.

Blank cells, text, and unusual data

Before interpreting a ranking, check the values in the comparison range. Blank cells and text entries are not numeric scores for ranking purposes, so a note such as “Absent” in the score column is not a substitute for a number. Values that look like numbers but are stored as text can also behave differently from genuine numeric values. Use Excel’s number-format and conversion tools to correct the source data rather than assuming every displayed digit is a number.

If a formula should show nothing for an empty score cell, with scores in B2:B6 and the rank formula in C2, use =IF(B2="","",RANK.EQ(B2,$B$2:$B$6,0)) and fill down. The check prevents a blank row from displaying a rank. The formula still ranks nonblank entries against the stated B2:B6 range; it does not remove text or errors elsewhere in that range.

Also check for accidental duplicates, imported spaces, and error values in the source data. A repeated number may be a genuine tie, or it may be an unintended duplicate. If values come from another system, verify that they are numeric and that the range covers the intended records before drawing conclusions from the ranks.

Common mistakes to avoid

  • Using 1 for descending order: use 0 for highest-to-lowest. A 1 ranks the lowest value first.
  • Forgetting dollar signs: a reference such as B2:B6 can shift as you copy the formula. Lock it as $B$2:$B$6 when every row should compare against the same list.
  • Sorting only one column: this can detach scores from their names or other details. Sort the entire table or expand the selection.
  • Expecting unique ranks with ties: RANK.EQ intentionally gives equal values the same rank and skips subsequent positions.
  • Using the wrong comparison range: excluding new rows or including unrelated values changes the answer, even when the formula itself is valid.

For a quick check, identify the largest value in the reference and confirm that its rank is 1. Then inspect a tie, if one exists, and confirm that equal values receive equal ranks. These checks catch many direction and range mistakes before the ranking is used in a report.

Frequently Asked Questions

What is the formula to rank highest to lowest in Excel?
Use =RANK.EQ(number,ref,0). For example, with the value in B2 and the full comparison range in B2:B6, use =RANK.EQ(B2,$B$2:$B$6,0). The highest number receives rank 1.

Does Excel rank highest to lowest by default?
Yes. If you omit the optional order argument in RANK.EQ, Excel ranks in descending order. Adding 0 explicitly is often clearer, especially when someone else may need to review the formula.

Why does Excel skip a rank after a tie?
RANK.EQ assigns the same rank to equal values and leaves the next occupied position unused. Two first-place values are followed by rank 3. Use RANK.AVG if the desired result is the average position of tied values.

How do I rank lowest to highest?
Change the order argument to 1. With the value in B2 and the comparison range in B2:B6, use =RANK.EQ(B2,$B$2:$B$6,1). The smallest value then receives rank 1.

How can I show the highest score first?
Sort the complete data range with Data > Sort Largest to Smallest. A rank formula labels positions but does not rearrange rows; sorting is the appropriate choice when you want the worksheet’s records displayed in descending order.

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.