Excel formula: Sort numbers ascending or descending

Generic formula

=SMALL(data,ROWS(exp_rng))

Explanation

To dynamically sort a list of numbers in ascending order, you can a simple formula based on the SMALL function with an expanding range. In the example shown, the formula in cell C5 is:

=SMALL(data,ROWS($B$5:B5))

where "data" is the named range B5:B14

How this formula works

The SMALL function is meant to extract the "nth" smallest value from a set of data. The value for N is supplied as the second argument. To get the smallest value with SMALL, supply 1, to get the second smallest value, supply 2, and so on.

=SMALL(data,1) // 1st smallest
=SMALL(data,2) // 2nd smallest
=SMALL(data,3) // 3rd smallest

In the example shown, "data" is the named range B5:B14. In this example, the main challenge is to increment a value for nth. This is done by using an expanding range inside the ROWS function:

ROWS($B$5:B5)

As the formula is copied down the table, the range expands and the number or rows increases, with supplies an incrementing value.

Sort numbers in descending order

To sort numbers in descending order, simply replace the SMALL function with the LARGE function:

=LARGE(data,ROWS(exp_rng))

Like SMALL, the LARGE function extracts an "nth" value. However, rather than the "nth smallest" LARGE returns the the "nth largest".

Author

Dave Bruns

Related formulas

Basic numeric sort formula

To dynamically sort data that contains only numeric values, you can use a helper column and a formula created with the RANK and COUNTIF functions. In the example shown, the formula in D5 is: = RANK ( C5 , sales ) + COUNTIF ( $C$5:C5 , C5 ) - 1 where...

Basic text sort formula

To dynamically sort text values in alphabetical order, you can use use a formula based on the COUNTIF function. In the example shown, the formula in C5 is: = COUNTIF ( countries , "<=" & B5 ) where "countries" is the named range B4:...

Display sorted values with helper column

To retrieve and display values sorted with a helper column, you can use an INDEX and MATCH formula, with a little help from the ROWS function. In the example shown, the formula in F5 is: = INDEX ( sales , MATCH ( ROWS ( $D$5:$D5 ), sort , 0 )) which...

Random sort formula

To randomly sort existing values with a formula, you can use an INDEX and MATCH formula together with helper columns as shown in the screenshot. In the example shown, the formula in E5 is: = INDEX ( names , MATCH ( ROWS ( $D$5:$D5 ), sort , 0 ))...

Sort and extract unique values

To dynamically sort and extract unique values from a list of data, you can use an array formula to establish a rank in a helper column , then use a specially constructed INDEX and MATCH formula to extract unique values. In the example shown, the...

Related functions

Excel SMALL Function

The Excel SMALL function returns numeric values based on their position in a list ranked by value. In other words, it can retrive "nth smallest" values - smallest value, 2nd smallest value, 3rd smallest value, etc.

Excel LARGE Function

The Excel LARGE function returns numeric values based on their position in a list when sorted by value. In other words, it can retrive "nth largest" values - largest value, 2nd largest value, 3rd lagest value, etc.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.