How to Arrange Numbers in Ascending Order in Excel Using Formula: 3 Ways

Method 1 – Arranging Numbers in Ascending Order in Excel Using SMALL with ROWS and COLUMNS Functions

1.1 Sorting by Rows

Combine SMALL with the ROWS function to sort numbers by rows and arrange them in ascending order.

In the following dataset, you have some random numbers. Arrange these numbers in ascending order using a formula.

dataset of random numbers

Follow the steps below to perform the task.

Steps:

Select cell C7 => Insert the formula:

=SMALL($B$7:$B$15,ROWS($B$7:B7))

Press Enter.

Arranging Numbers in Ascending Order in Excel Using SMALL with ROWS Functions

Use the Fill Handle to copy the formula in the other cells below. It will return the numbers in ascending order.

using fill handle to copy formula

Combine the AGGREGATE & ROWS functions, arranging numbers in ascending order. We’ll get the same output as shown above. The formula will be:

=AGGREGATE(15,0,$B$7:$B$15,ROWS($B$7:B7))

1.2 Sorting by Columns

Combine SMALL with the COLUMNS function to sort numbers by columns and arrange them in ascending order.

In the following dataset, we have some random numbers in row 6. Arrange these numbers in ascending order in row 7.

5-dataset of numbers in a row

Follow the steps below.

Steps:

Select cell C7 => Write the formula:

=SMALL($C$6:$K$6,COLUMNS($C$6:C6))

Press Enter.

Arranging Numbers in Ascending Order in Excel Using SMALL with COLUMNS Functions

Use the Fill Handle to copy the formula into other cells on the right side. It will return the numbers in ascending order.

using fill handle to copy formula

Method 2 – Using SORT Function to Arrange Numbers in Ascending Order Along with Dataset

2.1 Sorting by Rows

Use the Excel SORT function to sort by rows and arrange numbers in ascending order along with the dataset.

You have some products and sales in dollars. Arrange the products based on the ascending order of sales.

dataset of products and sales

Learn the following steps.

Steps:

Select cell E7 => Type the formula:

=SORT(B7:C15, 2, 1)

Press Enter. It’ll spill the dataset in ascending order.

Arranging Numbers in Ascending Order in Excel Using SORT formula

2.2 Sorting by Columns

Steps:

Select cell C10 => Write the formula:

=SORT(C6:K7, 2, 1,TRUE)

Press Enter. It’ll spill the dataset in ascending order.

Using sort formula to Arrange Numbers in Ascending Order in Excel

2.3 Sorting by Multiple Columns

Insert the SORT function to sort by multiple columns to arrange numbers in ascending order in Excel.

The following dataset includes some regions, products, and sales. Arrange the dataset in ascending order of regions and sales.

dataset of region, products and sales

Follow the steps below to accomplish the task.

Steps:

Select cell F7 => Type the formula:

=SORT(B7:D20, {1,3}, {1,1})

Press Enter to spill the output. See the regions in ascending order, and the sales are also in ascending order in each region.

Using sort formula to Arrange multiple columns in Ascending Order in Excel

2.4 Sorting N Smallest Values

Use the SORT function with the INDEX and SEQUENCE functions to arrange N smallest numbers in ascending order along with the dataset.

You have some products and sales in dollars. Input the desired number for N in F6, and later sort and extract data of the N smallest sales values in ascending order.

dataset of products and sales to sort n smallest values

Learn the steps below.

Steps:

Select cell E10 => Type the formula:

=INDEX(SORT(B7:C15, 2, 1 ), SEQUENCE(F6), {1,2})

Press Enter. It’ll spill the data of the N smallest sales in ascending order.

using sort formula to sort n smallest values in Ascending Order in Excel

The following GIF shows the sorting of the data corresponding to the change in the value of N in F6.

gif of sorting n smallest values

2.5 Sort and Filter (Sorting with Criteria)

Apply the SORT function with the IFERROR and FILTER functions to sort with criteria and arrange numbers in ascending order.

You have some products and sales. We’ll sort only the products that meet specified criteria. Arrange in ascending order only those products whose sales are greater than $200.

dataset of products and sales

Follow the steps below to perform the task.

Steps:

Select cell E10 => Type the formula:

=IFERROR(IF(E7=">=", SORT(FILTER(B7:C15, C7:C15>=F7), 2), IF(E7=">",SORT(FILTER(B7:C15, C7:C15>F7),2), IF(E7="=",SORT(FILTER(B7:C15, C7:C15=F7),2), IF(E7="<=",SORT(FILTER(B7:C15, C7:C15<=F7),2), SORT(FILTER(B7:C15, C7:C15<F7),2))))), "")

Press Enter to spill the output. See the desired products in ascending order.

Using sort formula to sort with criteria

The following GIF shows the change in formula output whenever we change the criteria. It’ll arrange the numbers in ascending order accordingly.

gif of sorting with criteria

2.6 Dynamic Sorting with Table

Use the SORT function along with the Table feature to sort dynamically, including any new entry, and arrange in ascending order accordingly.

The methods we have described can capture changes in the existing dataset. However, when you add new entries after the last row of the dataset, they are not included in the formula output. This method will use the Table feature to overcome that issue.

Follow the steps to accomplish the task.

Steps:

Select the range B6:C15.
Go to Insert => Click Table.

creating table

The Create Table dialog box pops out.

pressing ok in dialog box

Press OK in the dialog. The cell range will be converted into a table. Table 3 is the newly created table name.

table output

Select cell E7 => type the formula:

=SORT(Table3, 2, 1)

Press Enter. It’ll spill the formula output.

using sort formula with table for dynamic sorting

The following GIF shows the addition of a new product Kiwi to the main dataset. The formula output also gets updated accordingly and places the product in an accurate order.

gif of dynamic sorting