Excel RANK Function

RANK returns the position of a number within a list of numbers: first, second, third, and so on, based on size. Microsoft replaced it in Excel 2010 with two newer functions: RANK.EQ, which calculates rankings exactly the same way, and RANK.AVG, which handles tied values differently. RANK still works in Excel 365 for backward compatibility with older workbooks, but it's a legacy function now. New formulas should use RANK.EQ or RANK.AVG instead.

Available in: Excel 365, Excel 2021, Excel 2019, and all earlier versions. Superseded by RANK.EQ and RANK.AVG since Excel 2010.

Syntax

=RANK(number, ref, [order])

Parameter	Required	Description
`number`	Yes	The value whose rank you want. Can be a number or a cell reference.
`ref`	Yes	The list of numbers to rank against. Text and blank cells in this range are ignored.
`order`	No	`0` or omitted ranks the list from largest to smallest, so the biggest number gets rank 1. Any nonzero value ranks smallest to largest instead.

⚠

order defaults to descending when you leave it out. That means the highest value gets rank 1, the opposite of what many people expect from a "ranking." If you're ranking something like race finish times, where the lowest number should win, you need to set order to a nonzero value explicitly.

Basic Example

You're ranking ten salespeople by total sales for the quarter. Column C holds each person's total.

=RANK(C2, C2:C11, 0)

// C2      = this salesperson's total sales
// C2:C11  = every salesperson's total, including C2
// 0       = descending order, so the top seller gets rank 1

This returns a number from 1 to 10. The salesperson with the highest total in C2:C11 gets 1, the next highest gets 2, and so on down the list. Copy the formula down the column, and each row ranks that row's value against the same full range, which only works correctly if ref is locked with $.

How RANK Works

Ties share a rank, and the next rank gets skipped

Two salespeople with identical totals both receive the same rank number. If two people tie for 2nd place, both show 2, and the person below them jumps straight to 4. No one gets rank 3. This is standard "competition ranking," and it's the same logic RANK.EQ uses.

Order controls direction, not the sort of your data

ref doesn't need to be sorted at all. RANK compares number against every value in ref and counts how many are larger (for descending) or smaller (for ascending) to calculate the position. You can leave your source data in any order: the ranking formula does the sorting logic internally.

Non-numeric values in ref are ignored, not zero

Text, blank cells, and logical values inside ref don't count toward the ranking at all. They're skipped as if they weren't there. A ref range of {85, "N/A", 92, 78} ranks as if it only contained three numbers.

Handling Errors

RANK is fairly forgiving. number doesn't even need to exist in ref. RANK calculates where it would rank if it were inserted into the list. Still, a couple of error types show up in practice.

Common causes of #VALUE! and #REF!:

number points to a cell containing text instead of a numeric value
ref was typed as a range that contains no numbers at all
the ref range was deleted or moved after the formula was written, breaking the reference
a merged cell or external link inside ref that Excel can't evaluate

=IFERROR(RANK(C2, C2:C11, 0), "Check data")

✓

If a rank looks wrong but no error appears, the formula probably calculated fine. The mistake is almost always an unlocked ref that shifted when the formula was copied down. Check the range first before assuming RANK is broken.

Notes & Gotchas

Why do two rows show the same rank?

Because the values are tied, and RANK gives tied values identical ranks by design. It then skips the next rank number to account for the tie, so two rows tied for 4th means the next distinct value gets rank 6, not 5. If you'd rather split the difference between tied ranks, that's exactly what RANK.AVG does instead.

Why did copying the formula down produce wrong rankings?

The most common cause is a ref argument that wasn't locked with $. When you copy =RANK(C2, C2:C11, 0) down to row 3, Excel shifts the relative range to C3:C12 unless you write it as $C$2:$C$11. Each row ends up ranking against a slightly different, incomplete list. Lock the range and the problem disappears.

Does RANK work with dates and negative numbers?

Yes. Dates are stored internally as serial numbers, so RANK compares them the same way it compares any other value. An earlier date is smaller than a later one. Negative numbers rank normally too: with descending order, -5 ranks higher than -10 since it's the larger of the two.

Should I still use RANK, or switch to RANK.EQ?

Use RANK.EQ. It takes identical arguments and produces identical results to RANK, so switching is a find-and-replace, not a rewrite. Microsoft maintains RANK only for compatibility with workbooks built before Excel 2010, and there's no calculation advantage to keeping the old name in a new formula.

Related Functions

Function	Use this when...
`VLOOKUP`	You need to look up a value rather than rank one.