Rank in Excel Formula: Use RANK.EQ and Handle Ties

Formulas & functions · Updated November 20, 2026

To find a number’s position in a list in Microsoft Excel, use RANK.EQ. For example, if scores are in B2:B7, enter =RANK.EQ(B2,$B$2:$B$7,0) beside the first score and fill the formula down. The 0 ranks the largest value as 1; use 1 to rank the smallest value as 1. You need a numeric list and a cell where you can display each result.

What You’ll Need

  • A worksheet containing the numbers you want to rank, such as scores, sales totals, or times.
  • An empty column for the rank results. In the examples below, numbers are in B2:B7 and ranks will go in column C.
  • Microsoft Excel for Microsoft 365 on Windows. RANK.EQ and RANK.AVG are also available in current Excel for Mac and Excel for the web, though the ribbon layout can differ slightly.

The examples use these six scores in B2:B7, in order: 92, 85, 92, 76, 85, and 68. Knowing the sample values makes it easier to check whether Excel returns the expected rankings. You can replace the sample range with your own data once the formula is working.

Step-by-Step

  1. Identify the values and their full range. Find the first and last cells containing the numbers you want to compare. In the example, the values are in B2:B7. Do not include a heading such as Score in the range. Decide whether a larger value should receive rank 1, as with test scores or sales, or whether a smaller value should receive rank 1, as with race times or costs.
  2. Choose an empty cell beside the first value. Click C2, beside the value in B2. Keeping results in a separate column preserves the original data and makes it easy to compare each number with its rank. If your worksheet already has content in column C, choose another empty column and adjust the destination cell accordingly.
  3. Enter the RANK.EQ formula for a highest-first ranking. With scores in B2:B7 and the first result in C2, enter =RANK.EQ(B2,$B$2:$B$7,0). Press Enter. Excel compares the value in B2 with every numeric value in the fixed range and returns its rank. Since 92 is the largest score, C2 should show 1.
  4. Understand the three arguments before filling down. RANK.EQ takes the number to rank, the reference range to compare against, and an optional order setting. Here, B2 is the number, $B$2:$B$7 is the comparison range, and 0 requests descending order. Zero is also the default if you leave out the third argument, but writing it explicitly makes the intended ranking direction clear. The dollar signs lock the comparison range when the formula is copied.
  5. Copy the formula to the other rows. Select C2, point to the small square at the cell’s bottom-right corner, and drag it down to C7. You can also copy C2, select C3:C7, and paste. Excel adjusts the number being ranked from B2 to B3, B4, and so on, while the dollar signs keep the comparison range at B2:B7. The results should be 1, 3, 1, 5, 3, and 6.
  6. Check what ties mean in the results. The two scores of 92 both receive rank 1, and both 85s receive rank 3. RANK.EQ gives equal values the same rank and leaves a gap after tied positions: there is no rank 2 after the two first-place scores, and no rank 4 after the two third-place scores. This is standard competition ranking, not a formula error.
  7. Switch to lowest-first ranking when appropriate. For data where the smallest number should be first, change the order argument from 0 to 1. With values in B2:B7 and the result in C2, enter =RANK.EQ(B2,$B$2:$B$7,1). Copy it down the column. For the sample scores, the results are 6, 4, 6, 2, 4, and 1. This is useful for ranking elapsed times, where a shorter time is better, or expenses, where a lower amount is preferred.
  8. Use the average-rank version if ties should share an average. RANK.AVG returns the mean of the positions that tied values would occupy instead of assigning each the same integer rank. With sample scores in B2:B7 and the result in C2, enter =RANK.AVG(B2,$B$2:$B$7,0) and fill down. The two 92s each receive 1.5, while the two 85s each receive 3.5. Choose this only when average positions suit your reporting rules; it does not break ties into unique ranks.
  9. Extend the range if you add more values. The example’s fixed range includes only rows 2 through 7. If you later add a score in B8, update the formulas’ comparison range to include B8, or use a range that already covers the expected data. Check that each copied formula still points to the complete set being ranked; otherwise, new values will not affect the existing ranks.

In Excel for Mac or Excel for the web, enter the formula in the cell and fill or copy it down in the same way; the exact controls for dragging or pasting can vary by interface. If Excel displays semicolons rather than commas in formulas because of your regional settings, follow the separator Excel uses in your installation. The formulas shown here use standard comma separators.

Common Mistakes

  • Forgetting to lock the comparison range. If you use B2:B7 without dollar signs and copy down, Excel shifts that range to B3:B8, B4:B9, and so forth. Those changing comparisons can produce inconsistent ranks. Use $B$2:$B$7 when every result should compare against the same list.
  • Choosing the wrong order. The order argument 0 or omitted ranks the largest number as 1. The argument 1 ranks the smallest number as 1. A correct formula can still appear wrong if its direction does not match what you mean by best or first.
  • Expecting ties to have consecutive unique ranks. RANK.EQ deliberately assigns tied values the same position and skips the next rank or ranks. If every row must have a distinct position, decide on a tie-break rule, such as a secondary score or date; RANK.EQ alone does not make tied values unique.
  • Including a text heading or the wrong cells. Keep labels out of the comparison range and verify that the first and last rows are correct. Text and empty cells are not ranked as numbers, but an incorrect range can still omit valid numeric entries.
  • Ranking numbers stored as text. Values that look numeric may have been imported as text, which can lead to unexpected results. Check whether they align or behave differently from other numbers, and convert the source cells to numbers before relying on the rankings.

Tips

  • Use a clear heading. Label column C Rank or Position so readers can tell what the numbers mean. If the direction is not obvious, add a note such as Highest value is 1.
  • Keep the ranking range aligned with the data. If you regularly add rows, consider converting the source list into an Excel table using Insert > Table. Table references can expand as rows are added, but check the formula and range behavior in your workbook after converting; the simple fixed-range examples are easiest to audit for a short list.
  • Sort only if you want to rearrange the records. A rank formula calculates positions without moving the original rows. To reorder the data itself, select the dataset and use Data > Sort. Include the related columns when sorting so names and their values stay together.
  • Choose the tie method before publishing results. RANK.EQ is familiar for competition-style rankings, while RANK.AVG reports average positions. State which method you used if the difference matters to classmates, coworkers, or readers of a report.
  • Use conditional formatting for a quick visual check. Select the rank cells and choose Home > Conditional Formatting to highlight top or bottom results. This changes how values appear, not the ranks calculated by the formula.

Troubleshooting

Excel shows #NAME? Check that the function is spelled RANK.EQ or RANK.AVG and that the dot is present. If a much older Excel version does not recognize RANK.EQ, try the legacy RANK function, which remains supported for compatibility. With a score in B2, the comparison range B2:B7, and highest-first ranking, enter =RANK(B2,$B$2:$B$7,0) in C2. Its arguments and ranking direction work like the equivalent RANK.EQ example.

Ranks look different in each row. Inspect the formula bar for a shifting comparison range. In C3, for example, the intended range should still be $B$2:$B$7, even though the value being ranked should refer to B3. Re-enter the first formula with dollar signs around both ends of the range, then copy it down again.

Excel returns an unexpected rank or a tie. Confirm the order argument and inspect the source cells for duplicate values, hidden decimal places, or imported text. Two displayed values may look the same while their underlying decimal values differ; increase the number of displayed decimal places to check. Conversely, genuinely identical values are supposed to share a rank with RANK.EQ.

The result is an error or the new rows do not count. Make sure the number reference points to a cell containing a numeric value, and verify that the comparison range includes all values. If your Excel uses semicolon argument separators, replace formula commas with semicolons when entering the formula. Do not include a header row as though it were a score.

Frequently Asked Questions

What is the basic formula to find rank in Excel?
Use RANK.EQ with the value to rank, the complete comparison range, and an order argument. For highest-first ranking, order is 0; for lowest-first ranking, it is 1. Lock a shared range with dollar signs before copying the formula.

Does RANK.EQ rank the highest number as 1?
Yes, when the order argument is 0 or omitted. Set order to 1 when the smallest number should receive rank 1, such as when ranking finish times from fastest to slowest.

Why does my ranking skip a number?
RANK.EQ gives tied values the same rank and leaves a gap after the tie. If two values are tied for first, the next distinct value is ranked third. That is expected behavior.

What is the difference between RANK.EQ and RANK.AVG?
RANK.EQ assigns tied values the same integer rank and skips subsequent positions. RANK.AVG assigns tied values the average of the positions they would have occupied, such as 1.5 for two values tied in first and second place.

Can I rank text values with RANK.EQ?
No. RANK.EQ is for numbers. If you need to order names or other text, use a text-sorting approach rather than treating the values as numeric ranks.

Will the rank formula update when a score changes?
Yes. Excel recalculates the rank when a referenced value changes. However, if you add a new row beyond the formula’s comparison range, update that range or choose a setup that includes the additional row.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.