How to Rank in Excel with RANK.EQ

How-to basics · Updated November 20, 2026

To rank numbers in Excel, enter =RANK.EQ(B2,$B$2:$B$6,0) in a cell beside the first number, then fill the formula down. This ranks the largest value as 1; change the final argument to 1 to rank the smallest value as 1. RANK.EQ is available in Microsoft 365 for Windows and returns the same rank to tied values.

A ranking is useful for comparing scores, sales, test results, times, or other numeric values without rearranging the original data. Before you start, decide whether high or low values should rank first, and check that the values you want to compare are numbers rather than text.

What You’ll Need

  • A Microsoft Excel workbook with a column or row of numeric values to compare.
  • An empty column or row where Excel can display the ranks.
  • A decision about whether the largest or smallest value should receive rank 1.

Step-by-Step

  1. Arrange and check your data. Put one comparable value in each cell of a range, such as sales figures in B2:B6, with an optional heading in B1. Keep the values you intend to rank together in one continuous range. Check for blanks, numbers stored as text, and entries that do not belong in the comparison. For example, do not include a subtotal in the range if it should not compete with individual results. The formula can rank decimal values as well as whole numbers.
  2. Choose the ranking direction. Use descending order when a larger number should be better: the largest value receives rank 1. This is the usual choice for sales, points, or grades. Use ascending order when a smaller number should be better: the smallest value receives rank 1. That often suits completion times, costs, or error counts. The order is controlled by the formula, not by how the source cells are sorted.
  3. Add a heading for the results. If your values are in B2:B6, type Rank in C1. A separate results column makes it easy to see which rank belongs to each original value and avoids changing the source order. The examples below use the values 92, 85, 92, 78, and 68 in B2:B6, respectively.
  4. Enter the ranking formula beside the first value. With values 92, 85, 92, 78, and 68 in B2:B6 and the first result going in C2, enter =RANK.EQ(B2,$B$2:$B$6,0). The formula returns 1 for the 92 in B2. RANK.EQ takes the value to rank, the comparison range, and an optional order argument. The zero means descending order, so the largest number is ranked first.
  5. Make the comparison range absolute. In the formula, the dollar signs in $B$2:$B$6 lock the full comparison range in place when you copy the formula. The reference B2 is relative, so it changes to B3, B4, and so on as the formula is filled down. Without the dollar signs, the comparison range would shift too, and later rows might be ranked against only part of the list.
  6. Fill the formula down the results column. Select C2, point to the small square at the lower-right corner of the selected cell, and drag it down to C6. You can also copy C2, select C3:C6, and paste. The ranks for the example values are 1, 3, 1, 4, and 5. Both 92s receive rank 1 because they are tied, and the next value receives rank 3; Excel does not assign rank 2 to a different value in this tie situation.
  7. Use ascending order when lower numbers should rank higher. With values in B2:B6 and the first output in C2, enter =RANK.EQ(B2,$B$2:$B$6,1). In the example, the 68 in B6 is rank 1, the 78 is rank 2, the 85 is rank 3, and each 92 is rank 4. The final argument 1 tells Excel to rank from smallest to largest. Fill this formula down in the same way as the descending version.
  8. Decide how ties should appear. RANK.EQ gives tied values the same rank and leaves a gap after the tie. If you want tied values to share the average of the positions they occupy, use RANK.AVG instead. With 92, 85, 92, 78, and 68 in B2:B6 and the result for B2 in C2, enter =RANK.AVG(B2,$B$2:$B$6,0). Each 92 returns 1.5 because the tied values occupy first and second place, whose average is 1.5. The 85 then returns rank 3. Fill the formula down to calculate the average rank for every value.
  9. Check the results against the original values. For descending ranks, the largest value should have rank 1; for ascending ranks, the smallest should. Confirm that all intended cells were included and that ties behave as you expect. If you want to display the items in rank order as well, sort the complete data table by the rank column, while keeping each row’s related information together. Sorting changes the display order; a rank formula calculates the position without sorting the source data.

Common Mistakes

Choosing the wrong order argument: Leaving the third argument out makes RANK.EQ use descending order. If lower values should win, specify 1. A result can be mathematically correct but misleading if the direction does not match your purpose.

Letting the comparison range move: If you copy a formula such as =RANK.EQ(B2,B2:B6,0) down, the comparison range shifts along with the row reference. The ranking may then change from row to row. Lock the comparison range with dollar signs, for example $B$2:$B$6, before filling down.

Expecting unique ranks when values tie: RANK.EQ deliberately gives equal values equal ranks. In the example, two first-place values mean the next rank is 3. If every item needs a unique position, you must define an additional tie-break rule, such as a secondary score or date. Do not alter the values just to force unique ranks unless that rule is meaningful.

Including the heading or unrelated values: The comparison range should contain the numeric observations, not the column label, a total, or data from another group. Including extra values changes the ranking, even if the formula itself contains no error.

Tips

  • Keep the rank formula in its own column and retain the original values. This makes the calculation easier to audit and reduces the risk of accidentally overwriting source data.
  • Use consistent units and comparable records. For instance, do not rank monthly sales figures against annual totals, or times recorded in minutes against times recorded in seconds.
  • When adding new records, update the comparison range so it includes them. If a list grows frequently, converting the data range to an Excel table can help manage expanding records, but check the formula references after adding rows.
  • Sort only after the ranks are calculated if you want to present a leaderboard. Select the whole table when sorting so names, dates, and other columns stay with their corresponding values.
  • Use RANK.EQ when tied values should share a position. Use RANK.AVG when reporting an average position for ties is more appropriate. Neither function decides how a tie should be broken for a competition or business rule.
  • For a quick visual check, compare the rank column with the values: in descending order, larger values should have equal or better (smaller) ranks. Tied values should show the same result when the same comparison range and order are used.

Troubleshooting

The formula displays an error: Check that the function name is spelled RANK.EQ or RANK.AVG, that the parentheses are balanced, and that the referenced cells contain usable numeric values. Excel formulas in this guide use commas between arguments. Some regional settings use semicolons as separators; if Excel rejects a correctly written comma-separated formula, use the separator expected by your installation.

A value that looks numeric ranks unexpectedly: It may be stored as text, especially if it was imported or preceded by an apostrophe. Check whether the cell is left-aligned unexpectedly or shows a warning indicator. Convert the entries to numbers using Excel’s available conversion prompt, or use Data > Text to Columns and finish the conversion if appropriate. Then recalculate and verify the result. Avoid converting values blindly if the column contains codes where leading zeros matter.

Some rows show the same rank or a gap appears: First check for duplicate values. RANK.EQ assigns equal ranks to ties, so gaps are normal. If values only look identical but have different decimal places, increase the displayed decimal places to inspect them. Formatting a number to show fewer decimals does not change its stored value or make two different values equal.

New records are missing from the ranking: Inspect the formula’s comparison range. A formula comparing B2 against $B$2:$B$6 will not include a new value entered in B7. Extend the range, then fill or copy the formula for the new row. If the range expands regularly, consider using an Excel table and verify that its calculated column includes new entries.

The ranking changes after sorting or copying: Confirm that the comparison range is absolute and that the formula still points to the intended value in its own row. When sorting, sort entire rows rather than just the value or rank column; otherwise, names and figures can become mismatched. If a result appears stale, press F9 to recalculate the workbook, unless calculation is set to automatic and already updates it.

Frequently Asked Questions

What is the difference between RANK.EQ and RANK.AVG?
Both compare a value with a range. RANK.EQ gives tied values the same rank and leaves a gap after them. RANK.AVG gives each tied value the average of the positions those values occupy. In the example, the two highest values receive rank 1 with RANK.EQ and rank 1.5 with RANK.AVG.

Does RANK.EQ sort my data?
No. It returns a rank in the cell containing the formula and leaves the source records in their current order. To display records from highest to lowest or lowest to highest, sort the entire table separately.

Can I rank the smallest number as number 1?
Yes. Add 1 as the third argument, as in =RANK.EQ(B2,$B$2:$B$6,1) when the values are in B2:B6 and the formula is in C2. The smallest value in the example, 68, returns 1.

Why does my rank skip a number?
With RANK.EQ, a tie uses up more than one position. If two values tie for first, both are rank 1 and the next value is rank 3. This is expected; use RANK.AVG if you prefer the tied values to receive an averaged position.

Can Excel rank text labels?
RANK.EQ and RANK.AVG rank numbers, not labels such as names or product categories. To rank people or products, rank their numeric scores or measurements, then keep each score linked to its corresponding label. Make sure sorting the results does not separate labels from their data.

Will the formula include hidden or filtered-out rows?
RANK.EQ compares against the cells in its specified range; filtering or hiding rows does not automatically remove those values from the comparison. If you need ranks based only on visible records, use a method designed for filtered data and verify its behavior for your Excel version rather than assuming the ordinary RANK.EQ formula will adjust.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.