How do I use an Excel formula to sort numbers in ascending or descending order?

By /

Using an Excel formula, you can easily sort numbers in either ascending or descending order in your spreadsheet. To do this, you will need to use the “SORT” function, which arranges the selected data in either ascending or descending order based on a specified criteria. Simply input the formula in the desired cell, referencing the range of numbers you want to sort, and choosing the appropriate order (either “1” for ascending or “-1” for descending). This will automatically rearrange the numbers in your selected range, making it easier to analyze and organize your data.

Excel: Formula to Sort Numbers in Ascending or Descending Order

You can use the following formulas to sort numbers in Excel in ascending or descending order:

Formula 1: Sort Numbers in Ascending Order (Smallest to Largest)

=SMALL($A$2:$A$13,ROWS($A$2:A2))

Formula 2: Sort Numbers in Descending Order (Largest to Smallest)

=LARGE($A$2:$A$13,ROWS($A$2:A2))

Both of these formulas will sort the numbers in the range A2:A13 in either ascending or descending order, depending on whether you use the SMALL or LARGE functions in Excel.

The following examples show how to use each formula in practice with the following column of numbers in Excel:

Example 1: Sort Numbers in Ascending Order in Excel

We can type the following formula into cell B2 to return the smallest value from the range A2:A13:

=SMALL($A$2:$A$13,ROWS($A$2:A2))

We can then click and drag this formula down to each remaining cell in column B to return the numbers from the range A2:A13 in ascending order:

Excel sort numbers in ascending order by using formula

Column B now displays the numbers in column A in ascending order (smallest to largest)

Note that this formula uses the SMALL function in Excel to return the nth smallest value in a range and then uses the ROW function to specify the value for n.

For example, the first value in column B represents the 1st smallest value in column A.

Then, the second value in column B represents the 2nd smallest value in column A.

Example 2: Sort Numbers in Descending Order in Excel

We can type the following formula into cell B2 to return the largest value from the range A2:A13:

=LARGE($A$2:$A$13,ROWS($A$2:A2))

We can then click and drag this formula down to each remaining cell in column B to return the numbers from the range A2:A13 in descending order:

Excel sort numbers in descending order by using formula

Column B now displays the numbers in column A in ascending order (largest to smallest).

Note that this formula uses the LARGE function in Excel to return the nth largest value in a range and then uses the ROW function to specify the value for n.

For example, the first value in column B represents the 1st largest value in column A.

Then, the second value in column B represents the 2nd largest value in column A.

And so on.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

x

Related terms:

  1. How can I use Window.orderBy() in PySpark to sort data in descending order?
  2. How can PySpark use Window.orderBy() in descending order?
  3. How do you sort numbers in Excel?
  4. How do I find the first 3 positive numbers in Excel when I have a column of numbers?
  5. How do I construct an Excel formula to return a specific value if a cell is between two numbers?
  6. Excel Formula: If Between Two Numbers then Return Value
  7. ASCENDING PATHWAY 1
  8. ASCENDING TRACT
  9. How can I sort data alphabetically in Excel while keeping rows together?
  10. How can I sort two columns in Excel to match each other?