Math & aggregation
How to Use RANK in Excel (RANK and RANK.EQ Explained)
RANK tells you where a number sits in a list: 1st, 2nd, 3rd, and so on. It is how you turn a column of scores, sales, or times into a leaderboard, ranking from highest to lowest or lowest to highest.
This guide covers RANK.EQ (the modern function), the older RANK it replaced, ranking in either direction, and the classic trick for breaking ties so two equal values do not share the same rank.
RANK syntax
=RANK.EQ(number, ref, [order]) | Argument | Required | What it does |
|---|---|---|
number | Required | The value whose rank you want, usually the current row. |
ref | Required | The list of numbers to rank within. Lock it with $ so it stays fixed when you fill down. |
order | Optional | 0 or omitted ranks largest first (descending); 1 ranks smallest first (ascending). |
Note: RANK.EQ replaced RANK in Excel 2010 but RANK still works for compatibility; both give identical results. RANK.AVG averages the rank for ties. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Rank highest to lowest
=RANK.EQ(B2, $B$2:$B$100) The largest value gets rank 1. Omitting the order argument defaults to descending. The $ locks the range so you can copy the formula down the column.
2. Rank lowest to highest
=RANK.EQ(B2, $B$2:$B$100, 1) Passing 1 as the order ranks ascending, so the smallest number is rank 1. Use this for race times, prices, or any metric where lower is better.
3. Break ties so ranks are unique
=RANK.EQ(B2, $B$2:$B$100) + COUNTIF($B$2:B2, B2) - 1 RANK.EQ gives ties the same rank; the COUNTIF counts how many times the value has appeared so far and adds an offset, so the first tie keeps the rank and later ones step up by one.
4. Use the legacy RANK
=RANK(B2, $B$2:$B$100, 0) RANK works in every version of Excel and returns the same result as RANK.EQ. Use it only when a file must open in Excel 2007 or earlier.
How to write RANK step by step
- 1
Click the result cell next to your first value and type =RANK.EQ(
- 2
Click the cell you want to rank (the current row), then type a comma.
- 3
Select the whole list of numbers, then press F4 to lock it with $ signs.
- 4
Type 0 for highest-first or 1 for lowest-first (or leave it blank for highest-first).
- 5
Close the bracket ) and press Enter, then copy the formula down the column.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#N/A | The number is not found in ref, often because ref does not include the value's own row. | Make sure ref covers every value you are ranking, including the current cell. |
#VALUE! | The number or part of ref is text rather than a real number. | Convert text-numbers to real numbers; RANK ignores text, logical values, and blanks. |
Ranks skip numbers | Tied values share a rank, so the next rank is skipped (two 1sts means no 2nd). | This is normal. To force unique ranks, add the COUNTIF tie-breaker, or use RANK.AVG to share a fractional rank. |
Ranks change when filled down | The ref range was not locked, so it shifts with each row. | Use absolute references ($B$2:$B$100) for ref by pressing F4 before copying down. |
Frequently asked questions
What is the difference between RANK and RANK.EQ?
They are functionally identical. RANK.EQ was introduced in Excel 2010 as the recommended name; RANK remains for backward compatibility. Both give tied values the same (equal) rank.
How do I rank without skipping numbers after a tie?
Add a COUNTIF tie-breaker: =RANK.EQ(B2, $B$2:$B$100) + COUNTIF($B$2:B2, B2) - 1. This gives tied values consecutive ranks instead of repeating one and skipping the next.
How do I rank smallest to largest?
Set the order argument to 1: =RANK.EQ(B2, $B$2:$B$100, 1). The smallest value then gets rank 1, which suits times, prices, and golf scores.
Does RANK work in Google Sheets?
Yes. RANK, RANK.EQ, and RANK.AVG all exist in Google Sheets with the same arguments and behavior.