To rank numbers in Excel from largest to smallest, enter =RANK.EQ(B2,$B$2:$B$7,0) in a new column and fill it down; the largest number receives rank 1. To physically rearrange the rows instead, select the data and choose Data > Sort, then sort the number column by Largest to Smallest.
What ranking largest to smallest means
A rank assigns each value a position relative to the other values in a list. In descending order, the highest value is first and gets rank 1, the next-highest gets rank 2, and so on. Ranking does not automatically move or rearrange the original rows: it displays a position beside each value while leaving the source data in place.
That distinction matters. A rank column is useful when you want to keep records in their existing order, compare results with another column, or show a person’s place in a competition. Sorting is better when you want to see the entire table ordered from highest to lowest. Both approaches can produce a largest-to-smallest view, but only sorting changes the display order of the records.
For example, a sales list might have a salesperson in column A and sales in column B. A rank in column C tells you each salesperson’s standing, while sorting the table by sales puts the top salesperson’s complete row first. Keep the names and sales together when sorting; otherwise, you can accidentally separate a number from the person or item it belongs to.
Use RANK.EQ to calculate the position
In Microsoft Excel for Microsoft 365 on Windows, RANK.EQ is the standard function for assigning a rank. Its third argument controls the direction: use 0, or leave the argument out, for descending order; use 1 for ascending order. The formula below assumes scores are in B2:B7, with the first score in B2.
With scores in B2:B7 and the rank formula going in C2, enter =RANK.EQ(B2,$B$2:$B$7,0) and fill it down through C7. For scores 92, 75, 92, 81, 68, and 88, the ranks are 1, 5, 1, 4, 6, and 3, respectively. The dollar signs lock the comparison range so it remains B2:B7 as the formula is copied down; the reference to B2 changes to B3, B4, and so forth.
The function’s first argument is the value being ranked, and its second is the full set of values to compare against. Include the complete range in every copied formula. If you accidentally compare each row with a different, shifting range, the results will not represent a consistent ranking. If your list grows, update the reference range or convert the data to an Excel table so it is easier to maintain.
Excel also retains the older RANK function for compatibility with older workbooks. For a new formula, RANK.EQ makes the equal-rank behavior explicit. Both use the same basic order argument for ranking a number against a reference list.
Sort the whole table from largest to smallest
If your goal is to reorder records rather than add rank numbers, use Excel’s sort command. Click a cell in the table, open Data > Sort, choose the column containing the values under Sort by, and select Largest to Smallest under Order. If the first row contains column names, make sure My data has headers is selected in the Sort dialog. Click OK to apply the sort.
For a quick sort, select a cell in the numeric column and use Data > Sort Largest to Smallest. If the selection is part of a continuous table, Excel generally asks whether to expand the selection; choose that option so the rest of each record moves with its value. If you have selected only the number cells and sort them alone, names or other related fields may stay in place and no longer match the right values.
Sorting changes the order of the rows in the source range, so consider making a copy first if the original order is meaningful. Excel does not provide an automatic way to restore the original sequence unless you have an undo available or saved an original-position column. Adding an ID or sequence column before sorting is a simple safeguard when you may need to return to the starting order.
The Data and Sort commands are available in Excel for Microsoft 365 on Windows and Mac, although ribbon layout and dialog appearance can vary slightly by platform. In Excel for the web, use the Data tab’s sort options. The key safety principle is the same in each version: sort the entire related table, not just one field.
How Excel handles ties
By default, RANK.EQ gives identical values the same rank. In the example with two scores of 92, both receive rank 1. The next score, 88, receives rank 3, not rank 2. This is the usual competition-ranking convention: tied positions are shared, and the following rank numbers are skipped.
If you want tied values to receive the average of the positions they occupy, use RANK.AVG. With scores in B2:B7, put the following formula in C2 and fill it down: =RANK.AVG(B2,$B$2:$B$7,0). For the two scores of 92 in the example, each result is 1.5, because they occupy first and second place; 88 then ranks 3. The average-rank method is useful when the average position is more meaningful than assigning every tie the same integer rank.
Sometimes a report requires a unique sequence even when values match. One option is to use the order of the rows to break ties. With scores in B2:B7 and the formula entered in C2, use =RANK.EQ(B2,$B$2:$B$7,0)+COUNTIF($B$2:B2,B2)-1 and fill down. In the example, the two 92s receive ranks 1 and 2, while 88 receives 3. The first occurrence of a tied score gets the higher position, so this rule depends on the existing row order.
A unique rank is not automatically a fair tie-breaker. If ties should be settled by another measure, such as a time, date, or secondary score, make that rule explicit and sort by both fields or use a suitable formula based on both fields. Do not imply that two equal values are intrinsically different merely because their rows appear in a particular order.
Handle blanks, text, and changing data
Blank cells and nonnumeric entries can make a ranking look surprising. RANK.EQ ranks numeric values in its reference list; labels such as “pending” are not scores. If a cell that looks like a number is stored as text, check that it is truly numeric before relying on the results. Mixed data can also make the list harder to audit, so keep labels in a separate column and use a consistent number format for the values.
To leave the rank cell empty whenever the corresponding score is empty, rather than displaying a rank for an empty row, use an IF check. With scores in B2:B20 and the formula in C2, enter =IF(B2="","",RANK.EQ(B2,$B$2:$B$20,0)) and copy it down. The formula returns a blank string for an empty score and calculates a rank otherwise. A real score of zero is still a number, so it is ranked rather than treated as a blank.
Keep an eye on the comparison range when new rows are added. If the formula compares B2 against B2:B20, a score entered in B21 will not be included until the range is extended. Excel tables can help: select the data and choose Insert > Table, then add a rank formula in the table’s rank column. Excel typically fills a calculated-column formula through the table and extends it as table rows are added. Check the formula references after creating the table, especially if the workbook has unusual layouts.
If a rank displays an error, first check for errors in the score or comparison range, then confirm that the value being ranked is a number and that the reference range is valid. Also make sure the formula uses commas between arguments in standard Microsoft 365 settings. Excel installations configured for some regional settings may use semicolons as separators; follow the separator style Excel expects on that computer.
Use SORT when you want a separate ordered result
There is a third option when you want an ordered copy without changing the source table: the SORT function. It is available in Microsoft 365 and Excel 2021 or later. It returns a sorted result in another area of the worksheet, leaving the original data where it is. This can be useful for a dashboard or summary view that should update when the source values change.
With names in A2:A7 and scores in B2:B7, enter =SORT(A2:B7,2,-1) in an empty cell. Excel returns the two-column range sorted by its second column in descending order, so the highest score appears first and each name stays with its score. The final argument, -1, means descending; 1 means ascending. Leave enough blank space below and to the right for the result to spill. If cells in that destination area are occupied, Excel may show a spill error.
Unlike a manual sort, a formula-based sorted result is a view, not a rearrangement of the original cells. It recalculates when the source data changes, but it cannot spill over existing content. SORT also does not create rank numbers; it only orders the returned values. If you need both a rank and a sorted display, use a rank column for the first purpose and a sort for the second.
Choose the right method for your task
Use a ranking formula when every record needs a position beside it and the original row order should stay intact. Choose a normal sort when you want the actual table rearranged, perhaps to review top performers or largest expenses. Use SORT when you want a live, separate sorted view and have a version of Excel that supports dynamic-array formulas.
- Need rank labels beside all records: use
RANK.EQ, and decide whether ties should share a rank. - Need the original table reordered: sort the entire table by the relevant numeric column, Largest to Smallest.
- Need a live sorted copy: use
SORTin Microsoft 365 or Excel 2021 and leave room for the result to spill. - Need unique positions for ties: define a consistent tie-break rule before assigning sequential ranks.
It is common to confuse “rank largest to smallest” with “sort largest to smallest.” Ranking calculates positions, while sorting changes the order. Decide which result you need before choosing a method, and check ties, blank rows, and the range boundaries before sharing the workbook.
Frequently Asked Questions
What is the Excel formula to rank largest to smallest?
Use =RANK.EQ(B2,$B$2:$B$20,0) when the value to rank is in B2 and the full comparison list is B2:B20. The zero sets descending order, making the largest value rank 1.
How do I rank smallest to largest instead?
Change the order argument from 0 to 1. With values in B2:B20 and the first formula in C2, use =RANK.EQ(B2,$B$2:$B$20,1); the smallest number receives rank 1.
Does RANK.EQ change the order of my data?
No. It returns a number showing each value’s position but leaves the rows where they are. Use Data > Sort to rearrange the table, or SORT to create a separate ordered result in supported Excel versions.
Why do two values have the same rank?
Equal values are ties, and RANK.EQ assigns them the same rank. The ranks after a tie are skipped, so two values tied for first are followed by rank 3. Use RANK.AVG if you want the average of the tied positions.
Can I rank numbers while ignoring blank rows?
Blank and nonnumeric cells in the comparison range do not count as numeric scores. To keep the output cell blank when its own score is blank, wrap the rank formula in an IF check, as shown above, and make sure the formula range covers the intended data.
Related
- Most Used Excel Keyboard Shortcuts for Everyday Work
- Monthly Employee Work Schedule Template Excel Guide
- Mirror Image Flip in Excel: What Works and How
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Power Query: Keep Leading ZerosYes. To keep leading zeros in Excel Power Query, make the affected column’s data type Text before loading…
- Excel Solver for Data Analysis: Setup, Models, and LimitsExcel Solver is an optimization add-in: it changes selected input cells to maximize, minimize, or reach a target…
- Google Sheets Budget Tutorial: Build a Monthly PlanTo make a monthly budget in Google Sheets, enter your take-home income, list spending categories, and add columns…
- Keep a Zero in Front of a Number in Excel: Simple FixesTo keep a leading zero in Excel, format the cell as Text before typing or importing an identifier,…
- Excel Middle Years Academy: What Families Should KnowExcel Middle Years Academy appears to be the name of a school or education provider, not a Microsoft…
- Google Sheets Scheduling: Create a Shared ScheduleTo use Google Sheets for scheduling, create a spreadsheet with one row per shift, appointment, or task; add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.