> ## Content Index
> Fetch the complete content index at: https://aabie.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to use the Excel RANK function to rank numbers
- URL: https://aabie.com/excel-rank-function-rank-numbers/
- Published: 2026-11-10T02:24:01.000Z
- Updated: 2026-11-10T02:24:01.000Z
- Author: Aabie Editorial Team
- Tags: Education

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 as `B2`.
- `ref`: the full range of numbers to compare against, such as `B2:B10`.
- `order`: optional. Use `0` or leave it out for highest-to-lowest ranking. Use `1` for 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.

1. Type a header such as `Rank` in `C1`.
2. Click `C2` and enter the ranking formula for the first score.

```text
=RANK.EQ(B2,$B$2:$B$10)
```

Formula

1. Press Enter. You should see the rank for the score in `B2`.
2. Fill the formula down through the rest of column C.
3. 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.

![A laptop shows a Microsoft Excel worksheet with a score list and a rank column.](https://tse1.mm.bing.net/th?q=Microsoft%20Excel%20score%20list%20on%20laptop&w=624&h=352&c=7)

## 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:

1. Enter the formula in the first row of your rank column.
2. Copy it down with the fill handle or by double-clicking the fill handle if the neighboring column has data.
3. 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.

![A Microsoft Excel worksheet is open on a laptop with a formula filled down a column.](https://tse1.mm.bing.net/th?q=Microsoft%20Excel%20formula%20copied%20down%20spreadsheet&w=624&h=352&c=7)

## 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 `RANK` or `RANK.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.EQ` works 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 `ref` range 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.EQ` when you wanted averaged ties: switch to `RANK.AVG` if 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.