Arranging numbers in ascending order in Excel using a formula means sorting numbers from smallest to largest by applying Excel formulas. Suppose we have some random numbers: 8, 3, 10, 5. If we arrange them in ascending order, we’ll get 3, 5, 8, 10.
In this Excel tutorial, we’ll learn how to arrange numbers in ascending order in Excel using formulas.
In the following overview image, we have some numbers and we arranged them in ascending order using a formula.
We can arrange numbers in ascending order in Excel using a formula with SMALL and ROWS or COLUMNS function; SORT function; and SORTBY function. We’ll also show how to arrange the dataset along with the numbers.
1. Arranging Numbers in Ascending Order in Excel Using SMALL with ROWS and COLUMNS Functions
In this section, we’ll arrange numbers in ascending order in Excel using a formula combining SMALL with either ROWS or COLUMNS function. We’ll show how to sort numbers by rows and also by columns.
1.1 Sorting by Rows
In this part, we’ll combine SMALL with the ROWS function to sort numbers by rows to arrange them in ascending order.
Let’s see the dataset first. Here, we have some random numbers. We’ll arrange these numbers in ascending order using a formula.
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.
- Use the Fill Handle to copy the formula in other cells below. It’ll return the numbers in ascending order.
Alternatively, we can combine the AGGREGATE & ROWS functions for 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
In this part, we’ll combine SMALL with the COLUMNS function to sort numbers by columns to arrange them in ascending order.
Let’s see the dataset first. Here, we have some random numbers in row 6. We’ll arrange these numbers in ascending order in row 7.
Follow the steps below.
Steps:
- Select cell C7 => Write the formula:
=SMALL($C$6:$K$6,COLUMNS($C$6:C6))- Press Enter.
- Use the Fill Handle to copy the formula in other cells on the right side. It’ll return the numbers in ascending order.
2. Using SORT Function to Arrange Numbers in Ascending Order Along with Dataset
In this section, we’ll arrange numbers in ascending order along with the dataset by creating a formula with the SORT function. We’ll show how to sort numbers by rows; columns; multiple columns; sort N smallest values; sort with criteria; and sort dynamically while adding new values.
The SORT function is available in Microsoft 365 and Excel 2021 only as of now.
2.1 Sorting by Rows
In this part, we’ll use the Excel SORT function to sort by rows to arrange numbers in ascending order along with the dataset.
Here, we have some products and sales in dollars. We’ll arrange the products based on the ascending order of sales.
So, 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.
2.2 Sorting by Columns
In this part, we’ll apply the SORT function to sort by columns to arrange numbers in ascending order. Learn the following steps.
Steps:
- Select cell C10 => Write the formula:
=SORT(C6:K7, 2, 1,TRUE)- Press Enter. It’ll spill the dataset in ascending order.
2.3 Sorting by Multiple Columns
In this part, we’ll insert the SORT function to sort by multiple columns to arrange numbers in ascending order in Excel.
In the following dataset, we have some regions, products, and sales. We’ll arrange the dataset based on the ascending order of regions 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. We’ll see the regions in ascending order and the sales are also in ascending order in each region.
2.4 Sorting N Smallest Values
In this part, we’ll use the SORT function with the INDEX and SEQUENCE functions to arrange N smallest numbers in ascending order along with the dataset.
Here, we have some products and sales in dollars. We’ll input the desired number for N in F6 and later sort and extract data of the N smallest values of sales in ascending order.
Learn the below steps.
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.
The following GIF shows the sorting of the data corresponding to the change in the value of N in F6.
2.5 Sort and Filter (Sorting with Criteria)
In this part, we’ll apply the SORT function with the IFERROR and FILTER functions to sort with criteria and arrange numbers in ascending order.
Here, we have some products and sales. We’ll sort only the products that meet specified criteria. Suppose, in this example, we’ll arrange in ascending order only those products whose sales are greater than $200.
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. We’ll see the desired products in ascending order.
The following GIF shows the change in formula output whenever we change the criteria. It’ll arrange the numbers in ascending order accordingly.
2.6 Dynamic Sorting with Table
In this part, we’ll use the SORT function along with the Table feature to sort dynamically so that it includes any new entry and arranges in ascending order accordingly.
The methods we described so far can capture changes in the existing dataset. However, when we add new entries after the last row of the dataset, they fail to include them 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.
The Create Table dialog box pops out.
- Press OK in the dialog. The cell range will be converted into a table. Here, Table3 is the newly created table name.
- Now, select cell E7 => type the formula:
=SORT(Table3, 2, 1)- Press Enter. It’ll spill the formula output.
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.
Read More: Sort Numbers with Letter Suffix in Excel
3. Applying SORTBY Function to Sort Numbers in Ascending Order in Excel
In this section, we’ll arrange numbers in ascending order in Excel with the SORTBY function.
The SORTBY function is available in Microsoft 365 and Excel 2021 only as of now.
Steps:
- Select cell E7 => Insert the formula:
=SORTBY(B7:C15,C7:C15,1)- Press Enter. It’ll spill the outputs in ascending order.
Read More: Put Numbers in Numerical Order in Excel
Download Practice Workbook
This article has shown how to arrange numbers in ascending order in Excel using a formula. Use the SMALL function with the ROWS or COLUMNS function to arrange only the numbers. But if you want to arrange the dataset along with numbers, use the SORT or SORTBY function. Leave a comment for any further queries.
<< Go Back to Sort Numbers | Sort in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
