How To Set Numbers In Ascending Order In Excel

Login
Cart

Training
Videos
Functions
Formulas
Shortcuts
Blog

Sort numbers ascending or descending

Excel formula: Sort numbers ascending or descending

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:

where "data" is the named range B5:B14

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:

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:

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

Related formulas

Excel formula: Basic numeric sort formula

Note: this formula is the set-up for a formula that can extract and display data using a predefined sort order in a helper column. One example here . The core of this formula is the RANK function, which is used to generate a rank of sales values,...

Excel formula: Basic text sort formula

This formula uses the "greater than or equal to" operator with text, something you might not have tried before. When Excel compares text, it decides which value is "greater" than another based rules that follow the ASCII numbering scheme. Inside...

Excel formula: Display sorted values with helper column

This formula replies on a helper column that already contains a sequential list of numbers to represent an established sort order. The numbers in the helper column are independent from the operation of this formula. As long as the sequence is...

Excel formula: Random sort formula

This formula depends on two helper columns. The first helper column holds random values created with the RAND() function. The formula in C5, copied down is: = RAND () The RAND function generates a random value at each row. Note: RAND is a volatile...

Excel formula: Sort and extract unique values

Note: the core idea of this formula is adapted from an example in Mike Girvin's excellent book Control+Shift+Enter . The example shown uses several formulas, which are described below. At a high level, the MMULT function is used to compute a numeric...

Related functions

Excel SMALL function

The Excel SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest...

Excel LARGE function

The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest...

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel video training

Quick, clean, and to the point.

Learn more