How to Rank in Excel From Top to Bottom

To rank values from highest to lowest in Excel, enter =RANK.EQ(B2,$B$2:$B$10,0) beside the first value, then fill the formula down. This assumes the numbers you want to rank are in B2:B10 and the first result will go in C2. The highest number receives rank 1; equal values share a rank. Ranking labels your data but does not rearrange the rows, so you can sort the table afterward if you want a top-to-bottom leaderboard.

What You’ll Need

A column of numeric values to rank, such as scores, sales totals, or times. The instructions below use Excel for Microsoft 365 on Windows.
A blank column beside the values for the ranks. Include a header such as Rank so it is clear what the results mean.
If you plan to sort the results, include the related names or other information in the same table. That way, the scores, ranks, and names stay together when the rows move.

Step-by-Step

Set up the columns. Put a clear heading above each column. For example, enter Name in A1, Score in B1, and Rank in C1. Place each person or item on its own row, starting in row 2. This example uses scores in B2:B10, but your list can be longer or shorter. Keep the score cells numeric; a number stored as text may not behave like a normal score.
Choose the ranking direction. For a ranking from top to bottom, the largest value should receive rank 1. In Excel, that means using a descending rank order. This is useful for high scores, highest sales, or largest totals. If you instead want the smallest time or cost to be rank 1, use ascending order; the steps below explain how to switch it.
Enter the formula beside the first value. With scores in B2:B10 and the first rank cell in C2, enter =RANK.EQ(B2,$B$2:$B$10,0) in C2. The first reference, B2, points to the value being ranked. The dollar signs keep the full comparison range fixed when you copy the formula. The final 0 tells Excel to rank in descending order, so the largest value is first.
Check the first result. Press Enter and compare the rank with the score. If B2 contains the largest score, C2 should show 1. If B2 is the third-highest distinct score, it should show 3 unless ties affect the position. The function ranks the value against the full range, not just against the rows above it. If the result seems wrong, check that the range includes every score and that the order argument is 0.
Fill the formula down the column. Select C2 and point to the small square at the lower-right corner of the cell, called the fill handle. Drag it down to the last data row, such as C10. You can also copy C2, select C3:C10, and use Home > Fill > Down. Check a few copied formulas: the value reference should change from B2 to B3, B4, and so on, while the comparison range should remain $B$2:$B$10.
Understand what happens with ties. RANK.EQ gives equal values the same rank. For example, if the four scores are 92, 85, 92, and 70, their ranks are 1, 3, 1, and 4. The next rank after a tie is skipped because the tied values occupy more than one position. This is expected behavior, not a calculation error. If shared ranks are appropriate, leave the formula as it is.
Sort the whole list if you want a leaderboard. Ranking alone does not move the rows. Select the complete data table, including the names, scores, and ranks, then choose Data > Sort. In the Sort dialog, select the score column, choose Values, and set the order to Largest to Smallest. Confirm that the data has headers if your selection includes the heading row, then apply the sort. Sorting by score makes the highest score appear at the top while keeping each name with its score.
Use ascending order only when that fits the goal. For a list where the smallest value should be rank 1, change the final argument from 0 to 1. For example, elapsed times often use this direction because a shorter time is better. Keep the value and comparison range references matched to your own sheet. Do not switch to 1 just because you want the ranks to display vertically; the direction controls which number is considered best.

If you require a different rank for every row, including rows with tied scores, use a tie-break rule rather than changing the normal meaning of RANK.EQ. With scores in B2:B10 and the first rank in C2, this formula gives repeated scores consecutive ranks in their existing row order: =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1. For example, two equal top scores receive ranks 1 and 2, and the next score receives rank 3. This makes the order unique, but the row order decides which tied item gets the better rank. If that is not a fair or meaningful tie-break, keep the shared ranks instead.

Common Mistakes

Leaving the comparison range relative. A formula such as =RANK.EQ(B2,B2:B10,0) may work in the first row, but after filling down the range shifts. Use dollar signs around the range so every row is compared with the same complete set of values.

Using the wrong order argument. RANK.EQ uses 0 for descending order and 1 for ascending order. If you omit the order argument, Excel uses descending order, but including 0 makes the intention explicit. A 1 will make the smallest value rank first.

Sorting only one column. If you sort scores without their corresponding names and other details, the rows no longer describe the same records. Select the entire table before sorting, or accept Excel’s Expand the selection prompt when it appears. Never choose to continue with the current selection if that would separate related columns.

Expecting tied values to get unique numbers. Standard RANK.EQ intentionally assigns the same rank to equal values and skips the next position. Decide whether shared positions or a defined tie-break are appropriate before changing the formula.

Including headings or unrelated numbers in the range. Rank only the data cells, not the column heading, totals, or notes. An extra numeric total can change every result, while text headings can lead to confusion about what the formula is comparing.

Tips

Convert a growing data range into an Excel Table by selecting it and choosing Insert > Table. A table helps keep headings and related columns together when you sort, and it can extend as you add rows. If you use a table, verify that any formulas or sort selections include the new records before relying on the leaderboard.

Keep the original score column and the rank column separate. That makes it easier to check how each rank was produced and to sort by either score or rank later. Use consistent number formats too: values that look like numbers but are stored as text can be overlooked or handled unexpectedly. If data comes from an import, check a sample of cells before filling the formula down the entire list.

For a quick check, sort the full table by score from largest to smallest and confirm the first row has rank 1. If there are ties at the top, more than one row may show 1. If the rank formulas are correct but your table is not sorted, you can still read the ranking in the Rank column; sorting is only for arranging the display.

On Excel for Mac, the RANK.EQ formula and dollar-sign references work the same way. The ribbon layout can differ slightly by version, but use the Data tab’s Sort command to arrange the table. In Excel for the web, use the Data tab’s sort options and make sure the full table is selected so associated details remain with each score.

Troubleshooting

Every result is 1 or the ranks look inconsistent: inspect the formula in the first and last rows. The score reference should move row by row, while the comparison range should stay fixed with dollar signs. Also confirm that the order argument is 0 for highest-to-lowest ranking.

The formula returns an error: check for a misspelled function name, unmatched parentheses, or a range that includes a heading in place of a score. Excel formulas in this guide use commas between arguments. In some regional settings, Excel expects semicolons instead; if Excel rejects a correctly written formula, use the separator configured for your installation.

A score appears to be ignored: select the cell and check whether the value is numeric rather than text. Re-entering the value as a number or correcting the imported data can resolve the issue. Also confirm that the score lies within the range used by the formula. RANK.EQ can rank a value against a range even if it is not present in that range, so a formula referring to the wrong cell may still return a plausible-looking rank.

The names no longer match the scores after sorting: undo the sort immediately if possible, then select the complete table and sort again. If the workbook has already been saved or further edits have been made, restore the correct associations from a backup or source data. Avoid sorting a single column independently when the other columns describe the same records.

New rows are missing from the ranking: the fixed range in the example ends at row 10. If you add data below that row, update the ranking range and fill the formula into the new rows, or use a table-based setup that expands with the data. Check the first and last rank after making the change.

Frequently Asked Questions

Does rank 1 mean the highest value?
Yes, when the formula uses descending order, shown by the final argument 0. The largest number gets rank 1. Use 1 when the smallest number should be first.

Why does Excel skip a rank after a tie?
RANK.EQ gives equal values the same position. If two values tie for rank 2, the next value is rank 4 because two entries occupy the second and third places. That is standard competition ranking.

Can I rank numbers from different columns?
Yes. Point the value reference to the cell you want to rank and set the comparison range to the full set of values that should compete. Make sure the range contains comparable numbers and does not accidentally include totals or a different category.

How do I make the ranking update when a score changes?
RANK.EQ recalculates when a referenced score changes, provided the formula includes that cell and the ranking range. If you add rows beyond the range, expand the range or use a table-based approach that covers the new entries.

Does the rank formula sort the rows automatically?
No. It returns a rank in the formula cell but leaves the row in place. Use Data > Sort on the complete table to display the records from largest score to smallest.

Should I use RANK.EQ or RANK.AVG?
Use RANK.EQ when tied values should share the same rank and the next position should be skipped. RANK.AVG gives tied values the average of the positions they occupy. Choose based on the ranking rule your list needs.

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.