How to use the Excel RANK function to rank numbers
To rank numbers in Excel, use `RANK.EQ` in modern Excel. It tells you where one value sits inside a list, such as first, second, or fourth place. If you want tied values to share the same rank, use `RANK.EQ`; if you want tied values averaged, use `RANK.AVG`.
What RANK does and which version to use
The Excel RANK function tells you where one number sits inside a group of numbers. In plain English, it answers, “How does this score compare with the rest of the list?”
In modern Excel, the usual choices are:
RANK.EQ: gives tied values the same rank.RANK.AVG: gives tied values the average of the ranks they would have taken.RANK: older compatibility function you may still see in existing files.
For new worksheets in Excel 2026, start with RANK.EQ unless you specifically want averaged ties. If you open an older workbook and see RANK, it usually behaves like RANK.EQ for equal values, but RANK.EQ is the cleaner choice for new work.
The exact syntax you need
The formula pattern is:
=RANK.EQ(number,ref,[order])
Here’s what each part means:
number: the single value you want to rank. This is usually one cell, such asB2.ref: the full range of numbers to compare against, such asB2:B10.order: optional. Use0or leave it out for highest-to-lowest ranking. Use1for lowest-to-highest ranking.
A common mistake is to mix up the single value and the range. number should point to one cell, and ref should point to the whole list you want to compare against.
Rank a score list step by step
Say you have exam scores in column B, from B2 through B10, and you want the rank in column C.
- Type a header such as
RankinC1. - Click
C2and enter the ranking formula for the first score.
=RANK.EQ(B2,$B$2:$B$10)- Press Enter. You should see the rank for the score in
B2. - Fill the formula down through the rest of column C.
- Check a couple of rows against the source scores to make sure the numbers line up.
This setup keeps the rank column beside the data, which makes it easy to compare the score and the rank on the same row.
Choose highest-to-lowest or lowest-to-highest
The order argument controls the direction of the ranking.
Use 0 or leave order blank when you want the largest number to get rank 1. That is the usual descending order for sales totals, test scores, or any list where higher values are better.
Use 1 when you want the smallest number to get rank 1. That is useful for times, costs, penalties, or other lists where lower values are better.
Examples:
- Descending:
=RANK.EQ(B2,$B$2:$B$10,0)or=RANK.EQ(B2,$B$2:$B$10) - Ascending:
=RANK.EQ(B2,$B$2:$B$10,1)
Rank 1 does not always mean the highest value. It means the top value only when you use descending order. With ascending order, rank 1 means the smallest value.
How ties are handled
With RANK and RANK.EQ, equal values get the same rank. The next rank is skipped.
For example, if two scores tie for second place, both get rank 2, and the next value becomes rank 4. That is expected behavior, not an error.
If you want tied values to receive an averaged rank instead, use RANK.AVG. That function gives each tie the average of the positions they would have occupied.
Use the version that matches the report you need:
RANK.EQ: same rank for ties, with gaps after ties.RANK.AVG: averaged rank for ties.
If someone else will read the sheet, make sure they agree with the tie rule before you fill the formula down.
Fill the formula down without breaking references
This is where most mistakes happen. When you copy the formula down, you want the comparison range to stay fixed. Use an absolute reference for the full list, like $B$2:$B$10, so every row ranks against the same set of scores.
A good pattern is:
=RANK.EQ(B2,$B$2:$B$10)
Here, B2 is a relative reference. That is correct, because each row should rank its own score. The range $B$2:$B$10 is absolute, so it does not shift when you fill the formula down.
If you forget the dollar signs, Excel may move the range as you copy the formula. Then row 3 might rank against B3:B11, row 4 against B4:B12, and so on. That drifting range is a common reason the results look believable but turn out wrong.
To fill down:
- Enter the formula in the first row of your rank column.
- Copy it down with the fill handle or by double-clicking the fill handle if the neighboring column has data.
- Check that the copied formula kept the same range and only the first cell reference moved down one row.
If the range changes as you copy, stop and fix the references before trusting the results.
Quick checks to confirm it worked
A fast check catches most problems before you rely on the column.
- In descending order, the largest number should have rank 1.
- In ascending order, the smallest number should have rank 1.
- Equal values should have the same rank when you use
RANKorRANK.EQ. - The ranks should make sense if you compare them with a small sorted copy of the data.
If the best score is not rank 1 in descending order, or the smallest score is not rank 1 in ascending order, the formula or the range is wrong. The two most common causes are a bad reference range and mixed data in the list.
Common mistakes and easy fixes
The biggest mistakes are simple, but they create confusing results.
- Sorting the data first: sorting and ranking are not the same thing. Sorting changes the display order; the formula assigns the rank.
- Mixing text or blanks into the rank range:
RANK.EQworks on numbers. If your range includes labels, blank cells, or text that looks like a number, the result can be misleading. - Using the wrong range size: the
refrange should cover the full set you want to compare. If you leave out rows, the rank is based on an incomplete list. - Forgetting absolute references: if the range drifts when copied, the ranks can change row by row.
- Using
RANK.EQwhen you wanted averaged ties: switch toRANK.AVGif that is the rule you need.
If the rank looks off, click one copied cell and read the formula in the formula bar. Confirm that the number reference moved down one row, while the comparison range stayed fixed.
Frequently Asked Questions
Can I rank a column without sorting it first?
Yes. Ranking and sorting are separate. The formula calculates rank from the values in the range, so the list does not have to be sorted first.
What if my rank range includes blanks or text?
The ranking functions are meant for numbers. Clean the range so it contains only the values you want to compare, or make sure any non-numeric cells are outside the referenced range.
Should I use RANK, RANK.EQ, or RANK.AVG in modern Excel?
Use `RANK.EQ` for the usual tie behavior, and `RANK.AVG` if you want tied values to receive averaged ranks. `RANK` may still appear in older workbooks, but `RANK.EQ` is the better default in modern Excel.