Summary

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

Generic formula

=SMALL(data,ROWS(exp_rng))

Explanation 

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 of rows increases, which creates an incrementing number.

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 "nth largest".

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.