Priti
We can use Microsoft Excel for arranging numbers in ascending or descending order by using SORT from the toolbar or just creating a table and then sorting them according to our desire. However, we will demonstrate arranging numbers in ascending order in Excel using formulas. In this article, we described 8 simple formulas to arrange numbers in ascending order in excel using formula.
📁 Download Excel File
You can download the Excel file used for the demonstration from the link below.
Learn to Arrange Numbers in Ascending Order Using Formula in Excel with These 8 Methods
Firstly, let us get acquainted with our dataset used throughout the article as an example. We have the names of the students and their marks on a test. Now, we want to sort their marks in ascending order, and we will use different formulas for sorting the marks in ascending order. Hence, we will use this dataset to demonstrate 8 simple formulas to arrange numbers in ascending order in excel using formula.
1. Using SORT Function
The SORT function arranges the values of a range or array. This method will use the SORT function to arrange in ascending order in Excel. But this function only works in Microsoft 365 and Excel 2021 versions. So, if you have any version of Excel other than these two, try other methods demonstrated below. Let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell E6 and insert the formula.
=SORT(B6:C10,2,1)
🔨 Formula Breakdown
Here, the formula we inserted in cell E6 is:
SORT(B6:C10,2,1)
👉 Here, B6:C10 is the array that we want to sort.
👉 Then, 2 is the index sort index denotes the row or column number to sort.
👉 Lastly, 1 is the sort order which means ascending order, and -1 means descending order. Since we want to arrange them in ascending order, we took 1.
- Then press Enter, and we will have our sorted data in ascending order.
📕 Read More: How to Sort by Last Name in Excel
2. Applying SORTBY Function
The SORTBY function arranges the values of an array or range depending on the contents of that array or range. This function is also unavailable in Excel except in Microsoft 365 and Excel 2021. Now, we will demonstrate how to apply the SORTBY function to arrange numbers in ascending order in Excel. Let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, insert the formula by selecting cell E6.
=SORTBY(B6:C10,C6:C10,1)
🔨 Formula Breakdown
Here, the formula we inserted in cell E6 is:
SORTBY(B6:C10,C6:C10,1)
👉 Here, B6:C10 is the array that we want to sort.
👉 Then, C6:C10 is the array based on which to sort.
👉 Lastly, 1 is the sort order which means ascending order, and -1 means descending order. Since we want to arrange them in ascending order, we took 1.
- Then press Enter, and we will have our sorted data in ascending order by applying the SORTBY.
📕 Read More: 4 Quick Ways to Create Custom Sort List in Excel
3. Using SORT Function to Sort by Column
This method will use the SORT function to sort our data by Column. We will use the previous dataset, but we arranged it horizontally to demonstrate this method. This function is unavailable in Excel except in Microsoft 365 and Excel 2021. Let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, insert the formula by selecting cell C8. Then press Enter, and we will have our sorted data in ascending order by applying the SORT.
=SORT(C5:G6,2,1,TRUE)
🔨 Formula Breakdown
Here, the formula we inserted in cell C8 is:
SORT(C5:G6,2,1,TRUE)
👉 Here, C5:G6 is the array that we want to sort.
👉 Then, 2 is the index sort index denotes the row or column number to sort.
👉 Next, 1 is the sort order which means ascending order, and -1 means descending order. Since we want to arrange them in ascending order, we took 1.
👉 Lastly, TRUE is the argument to sort by Column.
📕 Read More: How to Sort Alphabetically in Excel with Multiple Columns
4. Sorting Rows Using SMALL and ROWS Functions
This method will use the SMALL and ROWS functions to sort our data by Rows. The SMALL function returns the n-th smallest value in a dataset, and the ROWS function returns the row number from an array or reference. Let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell E6 and insert the formula.
=SMALL($C$6:$C$10,ROWS($C$6:C6))
🔨 Formula Breakdown
Here, the formula we inserted in cell E6 is:
SMALL($C$6:$C$10,ROWS($C$6:C6))
👉 Here, $C$6:$C$10 is the array from which the SMALL function finds the smallest value.
👉 Then, ROWS($C$6:C6) returns the row number from the array and this number works as the position from the smallest for the ROWS function.
- Then drag the Fill Handle or double-click on this, and the numbers will be arranged in ascending order.
📕 Read More: 5 Ways to Auto Sort When Data Is Entered in Excel
5. Sorting Columns using SMALL and COLUMN Functions
This method will use the SMALL and COLUMNS functions to sort our data by Columns. The SMALL function returns the n-th smallest value in a dataset, and the COLUMNS function returns the column number from an array or reference. Let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, insert the formula by selecting cell C8.
=SMALL($C$6:$G$6,COLUMNS($C$6:C6))
🔨 Formula Breakdown
Here, the formula we inserted in cell E6 is:
SMALL($C$6:$G$6,COLUMNS($C$6:C6))
👉 Here, $C$6:$G$6 is the array from which the SMALL function finds the smallest value.
👉 Then, COLUMNS($C$6:C6) returns the column number from the array, which works as the position from the smallest for the COLUMNS function.
- Then drag or double-click on the Fill Handle, and the numbers will be arranged in ascending order by columns.
📕 Read More: 6 Ways to Auto Sort When Data Changes in Excel
6. Employing AGGREGATE and ROW Functions
This method will employ the AGGREGATE and ROWS functions to sort our data in ascending order. The AGGREGATE function returns the aggregate in a dataset, and the ROWS function returns the row number from an array or reference. Now, let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, insert the formula in cell E6.
=AGGREGATE(15,0,$C$6:$C$10,ROWS($C$5:C5))
🔨 Formula Breakdown
Here, the formula we inserted in cell E6 is:
AGGREGATE(15,0,$C$6:$C$10,ROWS($C$5:C5))
👉 Here, the first argument of AGGREGATE function 15 denotes the SMALL function.
👉 Then, 0 is the option to ignore nested subtotal and AGGREGATE functions.
👉 $C$6:$C$10 is the first reference for which we want the aggregate value.
👉 Lastly, ROWS($C$5:C5) returns the row number from the array, which works as the position from the smallest for the ROWS function.
- Then drag or double-click on the Fill Handle of the cell, and the formula will be pasted on the rest of the cells.
7. Combining INDEX, MATCH, COUNTIF, and ROWS Functions
This method will combine the INDEX, MATCH, COUNTIF, and ROWS functions to arrange our data in ascending order. The INDEX function extracts a value and returns it from a table, range, or reference to a value. On the other hand, the MATCH function looks for a specific item within a set of cells, finds it, and then returns its position. Moreover, the COUNTIF function counts the number of cells based on a criterion, and the ROWS function returns the row number of the reference. Now, let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell E6 and insert the formula in the cell.
=INDEX($C$6:$C$10,MATCH(ROWS($C$6:C6),COUNTIF($C$6:$C$10,”<=”&$C$6:$C$10),0))
🔨 Formula Breakdown
Here, the formula we inserted in cell E6 is:
INDEX($C$6:$C$10,MATCH(ROWS($C$6:C6),COUNTIF($C$6:$C$10,”<=”&$C$6:$C$10),0))
👉 COUNTIF($C$6:$C$10,”<=”&$C$6:$C$10) counts the number of cells with a value less than or equal to in cells C6 through C10. The ampersand (&) merges the comparison operator for less than or equal to (<>).
👉 ROWS($C$6:C6)) returns the row number from the array, which works as the position from the smallest for the ROWS function, and the MATCH function looks for this value and returns the relative position of the item in the range.
👉 $C$6:$C$10 is the array where to look for the value, output from the MATCH function returns the reference for row number and the COUNTIF function gives the column number.
- Lastly, drag or double-click on the Fill Handle of the cell, and the formula will be pasted on the rest of the cells.
8. Utilizing SORT Function to Sort Multiple Columns
This method will use the SORT function to sort data for multiple columns. This function is unavailable in Excel except in Microsoft 365 and Excel 2021. Let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, insert the formula by selecting cell F6.
=SORT(B6:D12,{2,3},{1,1})
🔨 Formula Breakdown
Here, the formula we inserted in cell F6 is:
SORT(B6:D12,{2,3},{1,1})
👉 Here, B6:D12 is the array that we want to sort.
👉 Then, 2 and 3 denote the column numbers to sort.
👉 Lastly, 1 denotes the sort order which means ascending order for both columns.
- Then press Enter, and we will have our sorted data in ascending order for multiple columns by applying the SORT.
📕 Read More: Sort Multiple Columns Independently of Each Other in Excel
📄 Important Notes
🖊️ SORT and SORTBY functions are available only on Microsoft 365 and Excel 2021 versions. So, if you have any other versions other than these two try other methods for arranging numbers in ascending order.
🖊️ Here, we have used an exact match for the MATCH function. If the value does not match exactly, it will show a #N/A error. So, make sure that the values match precisely.
📝 Takeaways from This Article
📌 This article demonstrated eight formulas to arrange numbers in ascending order in Excel using formulas.
📌 In the first method, we showed SORT functions to arrange numbers in ascending order in Excel.
📌 Then, we demonstrated a formula that applies the SORTBY function to sort numbers in ascending order in Excel.
📌 Next, we showed the SORT function to sort by column in ascending order in Excel.
📌 After that, we demonstrated a formula that applies SMALL and ROWS functions to sort numbers in ascending order in Excel.
📌 Then, we showed SMALL and COLUMNS functions to sort by column in ascending order in Excel.
📌 In the next method, we employed the AGGREGATE and ROWS functions to sort numbers in ascending order in Excel.
📌 Then, we combined INDEX, MATCH, COUNTIF, and ROWS functions to arrange numbers in ascending order in Excel.
📌 Finally, we employed the SORT function to sort multiple columns in ascending order in Excel.
Conclusion
This article has demonstrated eight easy ways how to arrange numbers in ascending order in Excel using formula. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.
For more guides like this, visit Excelden.com.
