RANK Formula Generator
Rank a number against a list of values, largest first or smallest first.
RANK.EQ
Rank a number against a list of values, largest first or smallest first.
The cell whose position you want, usually the value on the current row. It has to be a real number, and it has to appear somewhere in the reference range.
The full list to rank against. Use dollar signs so the range does not shift when you fill the formula down.
0 ranks largest first, so the highest value is rank 1. Use 1 to rank smallest first. The argument is optional in Excel and defaults to 0.
=RANK.EQ(A2, $A$2:$A$100, 0)Worked Example
Five sales totals in B2:B6 (88, 95, 72, 95, 60), ranking the 88 in B2 highest first.
=RANK.EQ(B2, $B$2:$B$6, 0)Returns: Returns 3. The two 95s tie at rank 1, rank 2 is skipped entirely, so 88 comes out third.
Checks Before You Paste
- •Lock the reference range with dollar signs, as in $A$2:$A$100, before filling down. A relative range slides one row per copy, and every result is wrong in a way that still looks plausible.
- •order 0 means descending, so the biggest number gets rank 1; any non-zero value, conventionally 1, means ascending so the smallest gets rank 1. Leaving the argument off entirely is the same as passing 0.
- •Ties share the higher rank and the next rank is skipped: 95, 95, 88 ranks as 1, 1, 3 with no rank 2. RANK.AVG returns 1.5, 1.5, 3 instead. To force unique ranks use =RANK.EQ(A2, $A$2:$A$100, 0) + COUNTIF($A$2:A2, A2) - 1, where the half-locked range grows as you fill down and breaks ties by row order.
How RANK.EQ works
Availability: RANK.EQ and RANK.AVG were added in Excel 2010. The original RANK still works in every version including Microsoft 365 and behaves identically to RANK.EQ. Google Sheets has all three, and also PERCENTRANK for a position expressed as a percentage.
RANK.EQ answers one question: where does this number sit in that list? It compares a value against a range and returns its position, counting from the largest down by default, or from the smallest up if you ask. The list does not need to be sorted, and the formula does not move anything on the sheet - it just reports a position, which is what makes it safe to drop next to data other people are still editing.
The order argument is the one people get backwards. Zero, or leaving the argument out, ranks in descending order so the highest value is rank 1 - right for sales figures, scores and revenue. Any non-zero value ranks ascending so the lowest value is rank 1 - right for lap times, response times and error counts. Excel accepts 1, 2 or TRUE for ascending, since it only tests whether the argument is zero, which is why a stray value in that slot silently reverses the whole column.
Ties get the same rank and then the next rank is skipped: 95, 95 and 88 rank as 1, 1 and 3, with no rank 2 anywhere in the column. RANK.AVG instead shares the tied positions out evenly and returns 1.5, 1.5 and 3. Neither is wrong, but the choice changes what the maximum rank means - with RANK.EQ the largest rank is no longer the number of rows once ties exist.
Everything RANK looks at must be a number and must be inside the reference range. A value stored as text returns #N/A, and so does ranking a number that is not present in the range at all, which is the usual outcome of forgetting to lock the range before filling down. Text and blanks inside the reference are ignored rather than ranked, so a column that is half text produces ranks that stop well short of the row count.
Syntax
=RANK.EQ(number, ref, [order])| Argument | Required | What it does |
|---|---|---|
number | Required | The value whose position you want, usually the cell on the current row. It has to be a real number and it has to appear somewhere in ref, or the result is #N/A. |
ref | Required | The list to rank against, as a range or array. Text, logical values and blanks inside it are ignored. Lock it with dollar signs - $A$2:$A$100 - before filling the formula down. |
order | Optional | 0 or omitted ranks largest first. Any non-zero number ranks smallest first. Excel only checks whether the value is zero, so 1, 2 and TRUE all mean ascending. |
More worked examples
Lap times in seconds in A2:A6: 32.4, 30.1, 35.8, 30.1 and 33.0. Ranking A2 with the fastest lap as rank 1.
=RANK.EQ(A2, $A$2:$A$6, 1)Returns: Returns 3. The two 30.1 laps tie for first, rank 2 is skipped, and 32.4 comes third.
The 1 in the order slot is what makes lower better here. Leaving it out would rank the slowest lap as number 1.
The same lap times, but ranking one of the two tied 30.1 entries in A3 with RANK.AVG.
=RANK.AVG(A3, $A$2:$A$6, 1)Returns: Returns 1.5, the average of the two positions the tied values occupy. RANK.EQ on the same cell returns 1.
Ranking each salesperson inside their own region: regions in B2:B100, sales in C2:C100.
=SUMPRODUCT(($B$2:$B$100=B2)*($C$2:$C$100>C2))+1Returns: For a rep with 48,000 in a region where two colleagues beat that figure, returns 3.
RANK.EQ has no criteria argument, so a within-group rank is counted this way instead: how many rows in the same region are higher, plus one.
Common mistakes
- The reference range slides as you fill down
- =RANK.EQ(A2, A2:A100, 0) is correct on the first row and wrong on every row after it, because the range shrinks to A3:A101, A4:A102 and so on. The first few results look right, which is what makes it dangerous. Always write $A$2:$A$100.
- #N/A because the number is not in the range
- RANK requires the value itself to be present in ref. It returns #N/A when the range has slipped past the row, when the number is stored as text, or when you are trying to rank a figure calculated elsewhere. Check with =COUNTIF($A$2:$A$100, A2), which returns 0 in exactly those cases.
- A non-zero order argument reversing the column
- Anything other than 0 in the third slot means ascending, including a stray 2, a TRUE, or a cell reference that happens to hold a number. A whole leaderboard can come out upside down with no error at all, so check that your known top performer really shows rank 1.
- Ties break downstream lookups
- Building a top-ten list with =VLOOKUP(ROW()-1, ...) against a rank column falls apart as soon as two values tie, because the skipped rank has no row to find and returns #N/A. Either force unique ranks with the COUNTIF tie-breaker, or use LARGE with SORT and take the first ten rows.
- Hidden and filtered rows still count
- RANK reads the whole reference range regardless of what a filter is showing, so ranks on a filtered list refer to the full data set. That is often what you want, but if the ranks are meant to describe the visible subset only, build a filtered copy of the values first - SUBTOTAL has no ranking option.
Frequently Asked Questions
Because RANK.EQ gives every tied value the highest position it could occupy, then resumes counting after all of them. Two values tied at 1 both take rank 1, and the next value is rank 3 because two rows are already ahead of it. Nothing is missing - the skipped number is where the second tied row sits.
Add a counter that grows as the formula fills down: =RANK.EQ(A2, $A$2:$A$100, 0) + COUNTIF($A$2:A2, A2) - 1. The half-locked range covers only the rows above and including the current one, so the first of a tied pair adds 0 and the second adds 1. Ties are then broken by row order, which is arbitrary but consistent.
RANK and RANK.EQ are the same function under two names, the older one kept for compatibility with Excel 2007 and earlier. RANK.AVG differs only in how it handles ties: instead of giving them all the top position, it gives them the average of the positions they occupy, so two values tied for first both return 1.5.
Usually because the value is text rather than a number - a left-aligned figure with an invisible leading space is the classic case - or because the reference range no longer includes the row after being filled down without dollar signs. Test both with =ISNUMBER(A2) and =COUNTIF($A$2:$A$100, A2).
Not with RANK itself, which takes no criteria. Count the rows in the same group with a higher value instead: =SUMPRODUCT(($B$2:$B$100=B2)*($C$2:$C$100>C2))+1. Swap the comparison to < for an ascending rank within the group.
Related Tools
Sort Excel Online
Reorder the rows themselves once the ranks tell you the order you want.
COUNTIF Generator
The function behind the unique-rank tie-breaker and the #N/A diagnosis above.
SUMPRODUCT Generator
How the within-group ranking formula counts rows that beat the current one.
MEDIAN Generator
The middle of the list, when the question is about the distribution rather than one row.