Rank Low to High in Excel: Formulas, Sorting, and Tips

Formulas & functions · Updated November 19, 2026

Yes. To assign ranks with the lowest value as rank 1, use Excel’s RANK.EQ function with its order argument set to 1; to rearrange the actual rows, sort the data from smallest to largest. These actions are related but different: ranking adds a position number, while sorting changes the order in which records appear.

What ranking low to high means

A low-to-high ranking orders numeric values conceptually from smallest to largest. The smallest value receives rank 1, the next distinct value receives the next rank, and the largest value receives the highest rank. For example, for scores of 8, 12, and 20, ascending ranks are 1, 2, and 3.

Excel’s ranking functions do not move or reorder the source data. They calculate a result in another cell, so each value stays beside its original row unless you separately sort the table. This is useful when you want to keep the original order—for example, a list of employees in a fixed order—while displaying a rank beside each person.

Sorting is the better choice when the goal is to see the complete records in low-to-high order. If a row contains a name, date, and sales amount, sorting by sales moves the whole row together. Sorting only the sales column can separate amounts from the people or records they belong to, so keep the table intact when rearranging it.

Use RANK.EQ to assign ascending ranks

Suppose names are in A2:A6 and scores are in B2:B6. The scores are 12, 8, 15, 8, and 20. Enter the following formula in C2, then fill it down through C6:

With scores in B2:B6 and the value to rank in B2, enter:

=RANK.EQ(B2,$B$2:$B$6,1)

The final argument, 1, tells Excel to rank in ascending order. The formula returns 3 for 12, 1 for 8, 4 for 15, 1 for the other 8, and 5 for 20. The dollar signs keep the comparison range fixed as you copy the formula down; the reference to B2 changes to B3, B4, and so on.

To enter the formula, select C2, type it in the formula bar or directly in the cell, and press Enter. To copy it to the remaining rows, select C2 and drag the fill handle—the small square at the lower-right corner of the selection—down to C6. You can also copy C2, select C3:C6, and paste.

RANK.EQ is available in current Microsoft 365 versions of Excel. Its order argument is optional, but leaving it out ranks from largest to smallest, so include 1 when you want the smallest value to be rank 1. Using 0, or omitting the argument, produces the opposite direction.

Understand ties and gaps in the results

By default, RANK.EQ gives equal values the same rank. In the example, both scores of 8 receive rank 1. Excel then skips rank 2, so 12 receives rank 3. This is sometimes called competition ranking: if two entries tie for first, the next entry is third. It is expected behavior, not a calculation error.

If you would rather give tied values the average of the positions they occupy, use RANK.AVG. With the same scores in B2:B6, enter this in C2 and fill down:

With scores in B2:B6 and the value to rank in B2, enter:

=RANK.AVG(B2,$B$2:$B$6,1)

The two 8s occupy the first and second positions, so each receives rank 1.5. The remaining results are 3, 4, and 5 for 12, 15, and 20. Choose RANK.EQ when tied items should share a rank and subsequent ranks should have gaps; choose RANK.AVG when the tied positions should be averaged.

Sometimes a report needs every row to have a unique position even when values tie. One way to break ties is to use the order in which the values appear. With scores in B2:B6, enter this in C2 and copy down:

With scores in B2:B6 and the value to rank in B2, enter:

=RANK.EQ(B2,$B$2:$B$6,1)+COUNTIF($B$2:B2,B2)-1

The first 8 gets rank 1 and the second 8 gets rank 2; 12 then gets rank 3. This creates unique ranks, but the tie-breaker is row order, not a judgment that one tied record is better. If ties should be resolved by another measure, such as an earlier date, define that rule explicitly rather than relying on row order.

Sort the full table from smallest to largest

To rearrange records, click a cell in the numeric column and choose Data > Sort & Filter > Sort Smallest to Largest. Excel sorts the associated rows as well when it recognizes the data as a table or contiguous range. If Excel asks whether to expand the selection, choose Expand the selection to keep each record together.

Before sorting, check that the range has one header row and no entirely blank rows or columns breaking up the table. If the data has headers, make sure Excel treats them as headers rather than sorting them into the records. For more control, select a cell in the data and choose Data > Sort. In the Sort dialog, select the numeric column under Sort by, choose Cell Values, and set the order to Smallest to Largest. Use Add Level if you need a secondary sort, such as sorting equal sales totals by name.

A sort changes the visible order of the source records. If you need to preserve the original order, make a copy of the data first or add an index column that records the initial sequence. Sorting can also affect formulas or references elsewhere in a workbook, so check dependent calculations and reports after rearranging important data.

Use SORT for a formula-driven ordered copy

In Microsoft 365 and Excel 2021 or later, SORT can return a sorted copy of a range without moving the original records. If names are in A2:A6 and scores are in B2:B6, enter this formula in an empty cell, such as D2:

With names in A2:A6 and scores in B2:B6, enter:

=SORT(A2:B6,2,1)

The result spills into two columns and five rows, ordered by the second column—the scores—from smallest to largest. In this example, both records with a score of 8 appear first, followed by 12, 15, and 20. The formula produces a separate view; it does not change the source range. Leave enough empty cells below and to the right for the result, or Excel may show a spill error.

SORT is convenient when the displayed list should update as source values change. For example, if a score changes, the formula result recalculates and the copied view reorders. The function sorts the range you provide, so include all columns that belong with each record. Sorting only B2:B6 would return a list of numbers, not a corresponding list of names.

Choose between ranking, sorting, and a sorted copy

The right method depends on what the result is meant to show:

  • Use RANK.EQ when you need a rank beside each original record and want the source order to remain unchanged.
  • Use RANK.AVG when tied entries should share the average of their occupied positions.
  • Use the Sort command when you want to rearrange the original table and inspect records from lowest to highest.
  • Use SORT when you want a separate, formula-driven ordered view and have Microsoft 365 or Excel 2021 or later.

These methods can also be combined. A report might keep the original list, add an ascending rank with RANK.EQ, and show a sorted copy elsewhere for easier reading. Keep in mind that a rank is not the same as a percentile or a position expressed as a percentage. If you need a percentage-based comparison, select a function intended for percentiles or percent ranks rather than interpreting a rank number as a percentage.

Common problems and how to fix them

If the smallest value is receiving the largest rank, check the third argument of RANK.EQ. An argument of 1 means ascending; 0 or an omitted argument means descending. Also confirm that the formula compares each row’s value with the complete range and that the range has dollar signs so it does not shift while being copied.

If numbers appear to be ignored, they may be stored as text. This can happen after importing data from another system. Check whether values are aligned or flagged differently from ordinary numbers, and convert the affected cells to numeric values before ranking. Text entries and blank cells are not numeric values to rank, while a numeric zero is a real value and can appear at the low end of an ascending ranking.

If a formula returns an error, inspect the referenced range for existing errors and verify that the value-to-rank cell contains a usable number. For SORT, a spill error usually means that one or more cells needed for the returned array are occupied. Clear the obstructing cells or place the formula where the output range is empty. If sorting appears to detach names from their scores, undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then sort the full table rather than one column alone.

For repeated reporting, format the data as an Excel table using Insert > Table. Tables make it easier to include new records in a consistently structured range, and their headers help identify columns during sorts. A manually written rank formula with a fixed range such as B2:B6 will not automatically include a new row added below that range; check and extend such ranges when the data grows.

Frequently Asked Questions

How do I rank numbers from lowest to highest in Excel?
Use RANK.EQ with 1 as the third argument. For a value in B2 compared with B2:B20, use =RANK.EQ(B2,$B$2:$B$20,1). Copy the formula down to rank the other values.

How do I sort a column from lowest to highest?
Click a cell in the column and choose Data > Sort & Filter > Sort Smallest to Largest. If the column belongs to a table, expand the selection when prompted so each row stays together.

Why does Excel skip a rank after a tie?
RANK.EQ assigns the same rank to equal values and leaves a gap afterward. For instance, two values tied at rank 1 are followed by rank 3. Use RANK.AVG if you want tied values to receive the average of their positions instead.

Can I rank values without changing their order?
Yes. Put a RANK.EQ or RANK.AVG formula in a separate column beside the data. Ranking calculates positions but does not rearrange the source rows.

Can Excel automatically show a low-to-high copy?
Yes. In Microsoft 365 or Excel 2021 and later, SORT can return an automatically updating copy of a range in ascending order. Place its formula in an empty area with enough room for the returned rows and columns.

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.