RANK Excel Function: Examples, Ties, and Sort Order

To rank a value in Excel, use RANK.EQ(number,ref,[order]): for example, with scores in B2:B6, =RANK.EQ(B2,$B$2:$B$6,0) returns B2’s position from highest to lowest. Use RANK.AVG instead when tied values should receive their average position. Both functions rank values without rearranging the data.

What the RANK functions do

Ranking answers a straightforward question: where does a number sit compared with the other numbers in a list? Excel provides two current functions for this task: RANK.EQ and RANK.AVG. The first gives equal values the same rank; the second gives tied values the average of the positions they occupy.

For example, if the scores are 92, 85, 92, 76, and 88, the two 92s share first place under RANK.EQ. The next score is ranked third, not second, because two entries occupy first place. This is called competition ranking: the resulting ranks are 1, 1, 3, 4, and 5. With RANK.AVG, the tied 92s receive 1.5, the average of the first and second positions; the remaining values receive ranks 3, 4, and 5.

Ranking is not the same as sorting. A rank formula returns a number beside each value, leaving the original row order intact. That is useful when scores must stay paired with names, dates, or other record details. To physically reorder records, use Excel’s Sort commands or a sorting function instead.

RANK.EQ syntax and sort order

The syntax is RANK.EQ(number,ref,[order]). The number is the value whose position you want, ref is the list of values to compare it with, and order is optional. Use 0, or leave order out, to rank from largest to smallest. Use 1 or another nonzero value to rank from smallest to largest. The comparison range should include the number being ranked.

Assume B2:B6 contains 92, 85, 92, 76, and 88, in that order. With B2 holding 92 and the full comparison list in B2:B6, =RANK.EQ(B2,$B$2:$B$6) returns 1. The omitted order argument means descending rank. With B3 holding 85 and the same comparison range, =RANK.EQ(B3,$B$2:$B$6,0) returns 4: two scores of 92 and the score of 88 are higher.

To reverse the direction, use the same B2:B6 list and set order to 1. With B2 holding 92, =RANK.EQ(B2,$B$2:$B$6,1) returns 4. The values 76, 85, and 88 come before 92, while the other 92 ties with it. With equal values, RANK.EQ gives both the same rank, so both 92s receive rank 4.

Practical examples with a score list

Here is a small example dataset to make the results concrete:

B2: 92
B3: 85
B4: 92
B5: 76
B6: 88

To add a rank beside each score, put the formula in C2 and fill it down through C6. With the scores in B2:B6, enter =RANK.EQ(B2,$B$2:$B$6,0) in C2. The dollar signs keep the comparison range fixed when the formula is copied, while B2 changes to B3, B4, and so on. The returned ranks are 1, 4, 1, 5, and 3, respectively.

For a version that treats ties as average positions, use the same layout and comparison range. Enter =RANK.AVG(B2,$B$2:$B$6,0) in C2 and copy it down. The results are 1.5, 4, 1.5, 5, and 3. The 92s occupy positions one and two, so each receives (1 + 2) / 2, or 1.5. The remaining values are unaffected because they are not tied.

A rank can also be calculated for a value that is not stored in the list. With comparison scores in B2:B6 and a score of 90 to evaluate, =RANK.EQ(90,$B$2:$B$6,0) returns 3. The two 92s and the 90 occupy rank positions one, one, and three under competition ranking; the 90 is below both 92s and above 88. This can help evaluate a proposed result against an existing set.

Handling ties and creating unique positions

Ties are not errors; they reflect equal values. Choose RANK.EQ when equal measurements should be treated equally, such as matching test scores. Choose RANK.AVG when an average position is more useful for statistical reporting. Decide which convention fits the purpose before comparing ranks across reports, because the functions produce different numbers for tied entries.

Sometimes a report needs a distinct position for every row even when values match. One option is to break ties by the order in which the values appear. With scores in B2:B6, enter this in C2 and fill down: =RANK.EQ(B2,$B$2:$B$6,0)+COUNTIF($B$2:B2,B2)-1. For the two 92s, the first occurrence receives 1 and the second receives 2. In this sample the full results are 1, 4, 2, 5, and 3. The COUNTIF range expands as the formula is copied, counting earlier appearances of the current score.

This tie-break method is not a claim that the first row is objectively better than the next one; it simply makes the output unique according to row order. If a particular rule should break ties, such as an earlier date or a secondary score, encode that rule explicitly rather than relying on row position. For a ranked display, you can also sort the original records by score and then by the chosen secondary field.

Choosing between RANK.EQ, RANK.AVG, and RANK

RANK.EQ is usually the clearest choice for ordinary ranking because shared values receive equal ranks and later ranks skip positions. RANK.AVG is appropriate when tied values should share the mean of their occupied positions. Neither function resolves a tie into a unique order by itself.

Excel also retains the older RANK function for compatibility with older workbooks. It follows the equal-rank behavior of RANK.EQ. For new formulas, using RANK.EQ makes the tie behavior explicit; when maintaining a workbook that already uses RANK, there may be no need to replace it if it produces the intended results. Both newer functions have been available since Excel 2010 and are supported in Microsoft 365.

Do not confuse ranking with finding the value at a given position. For example, RANK.EQ tells you the position of a score, while functions such as LARGE can return the score at a specified descending position. A rank result of 3 does not itself return the third-largest value; it says that the evaluated value has rank 3 under the selected rule.

Common problems and reliable formula setup

A frequent mistake is allowing the comparison range to move when filling a formula down. If the scores are in B2:B6, use absolute references for that range, as in $B$2:$B$6. Keep the individual value reference relative, as in B2, so each row is ranked against the same complete list. Without the dollar signs, the range may shift and each copied formula could compare against a different, incomplete set.

Wrong direction: If the highest value should be first, omit order or use 0. If the lowest should be first, use 1.
Unexpected gaps: With RANK.EQ, ties cause skipped positions. Two first-place values are followed by rank 3.
Unexpected decimals: With RANK.AVG, tied values can have fractional ranks because their positions are averaged.
Different records in the range: Check that the reference includes all intended values and excludes unrelated numbers, such as a total or a note typed as a number.
Formula shows as text: Check that the cell is not formatted as Text, and that the formula begins with an equals sign. If needed, change the format to General and re-enter the formula.

RANK functions compare numeric values. Text entries in the reference are not treated as ranked numeric observations, so verify that imported numbers are actually stored as numbers. A value that looks numeric but is stored as text can be overlooked or lead to confusing results. Also check for errors in the source cells, since an error in a formula’s input or reference can interfere with a calculation. Cleaning and validating the source data is often more important than changing the rank formula.

Ranking in a changing list

If new rows are added regularly, a fixed range such as B2:B6 will not automatically include entries below row 6. One simple approach is to select the data and create an Excel table with Insert > Table. Table columns expand as rows are added, and formulas entered in a table column are generally filled through that column. You can then use structured references in a rank formula, but the exact reference depends on the table and column names, so check the names Excel assigns.

Another approach is to include a larger range, provided it does not contain unrelated numeric data. For a list expected to use rows 2 through 100, the comparison range can cover B2:B100. Blank cells do not become ranked scores, but a broad range should still be limited to the relevant data area. If the range includes extra numeric values, those values affect every rank even if they are not visible beside the current records.

When using Excel for the web, the same basic RANK.EQ and RANK.AVG formulas work. The formulas also work in Excel for Mac; the examples here use comma argument separators, although regional settings can require semicolons instead. On Windows, F4 can cycle a selected cell reference through absolute and relative reference styles while editing a formula. On many Mac keyboards, use Command+T to cycle reference styles in Excel; keyboard behavior can vary with macOS and keyboard settings.

Frequently Asked Questions

How do I rank numbers in Excel from highest to lowest?
With the value in B2 and the comparison list in B2:B10, enter =RANK.EQ(B2,$B$2:$B$10,0). Zero requests descending order, so the largest number receives rank 1.

How do I rank numbers from lowest to highest?
Use 1 as the order argument. With the value in B2 and scores in B2:B10, enter =RANK.EQ(B2,$B$2:$B$10,1). The smallest number receives rank 1.

Why does Excel skip a rank after a tie?
RANK.EQ gives tied values the same position and then continues after all occupied positions. If two values tie for first, the next value is third. Use RANK.AVG if the tied entries should instead receive the average of their positions.

What is the difference between RANK.EQ and RANK.AVG?
RANK.EQ assigns the same rank to tied values and skips the positions they occupy. RANK.AVG assigns each tied value the average of those positions, which can produce a decimal rank.

Does RANK sort my Excel data?
No. It calculates a rank value but leaves rows in their existing order. Use Excel’s sorting tools to rearrange records while keeping their related columns together.

Can I use RANK to assign a unique position to every value?
Not by itself when values tie. Add a deliberate tie-break rule, such as counting earlier occurrences with COUNTIF, or sort by a secondary field. A unique result depends on the tie-break rule you choose.

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.