Shakil Ahmed
Depending on different circumstances you may need to arrange numbers, among other things, in a certain order. You can quite easily do the task using several formulas. Today we are going to show you how to arrange numbers in ascending order in Excel using formula.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
2 Ways to Arrange Numbers in Ascending Order with Excel Formula
Before starting the tutorial, let’s get to know about the workbook which is the base of our examples.
We have a basic table that contains the top scorers with their scores across the different European leagues. The Goals column contains the numbers, so our sorting or arranging will be done based on this column.
Note that this is a basic table with a simple scenario, in real cases, you may encounter a much larger and more complex data set.
1. Use Combination of Different Excel Functions to Arrange Numbers in Ascending Order
You can see in our chosen basic data set we have a single column for numbers; Goals.
Let’s imagine a scenario, where we want to sort the number of goals scored.
We have introduced a separate column Goals Sorted. We aim to store the goals scored by the players in ascending order.
1.1 Combination of INDEX, MATCH, ROWS & COUNTIF Functions
In Excel, you may be familiar with the INDEX-MATCH combination. Yes, it is one of the most powerful and widely used combinations in Excel. This combination is suitable for our task “arrange numbers in ascending order using formula”. Let’s see how to do that.
Firstly, The INDEX function returns the value at a given location in a range or array.
=INDEX (array, row_num, [col_num], [area_num])array: A range of cells, or an array constant.
row_num: The row position in the reference or array.
col_num: The column position in the reference or array.
area_num: The range in reference that should be used. This is an optional field.
If you use the row_num, then the col_num will be optional.
Additionally, The MATCH function is used to locate the position of a lookup value in a row, column, or table.
=MATCH (lookup_value, lookup_array, [match_type])lookup_value: The value to match in lookup_array.
lookup_array: A range of cells or an array reference.
match_type: This argument specifies how Excel matches lookup_value with values in lookup_array. 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.
We can arrange the numbers using this INDEX-MATCH combination. However, we will use the ROWS and the COUNTIF function as well. Let’s see the generic formula.
=INDEX(array, MATCH(ROWS(starting_row),COUNTIF(array,criteria),0))Here we have set the row_num for INDEX using MATCH. Follow the steps given below to arrange numbers using these functions in your dataset.
Steps:
- Firstly, select Cell E5 and insert the following formula.
=INDEX($C$5:$C$16,MATCH(ROWS($C$5:C5),COUNTIF($C$5:$C$16,"<="&$C$5:$C$16),0))- Then, press Enter. However, if you are using previous versions rather than Excel 365, then you need to use Ctrl + Shift + Enter to execute the formula since it is an array formula.
- After that, drag-down the Fill Handle tool to get the other values of goal in ascending order.
🔎 How Does the Formula Work?
- Here, we have inserted the Goals column as the array.
- Firstly, in the MATCH function, we have used ROWS. Using this function, we have produced incremental rows. This will be the lookup_value for MATCH.
- After that, we have used the COUNTIF function to find the lookup_array. Within the function, we have inserted the array and the criteria condition.
- COUNTIF($C$5:$C$16,”<=”&$C$5:$C$16) compares all the values in the same row with each other and returns an array of their relative ranks.
- Then, this array will be our lookup_array here in the MATCH function.
- Additionally, we have used 0 as match_type.
Oh! We have encountered a few error values, though the numbers have been sorted. Do you understand why this happens?
N/A stands for Not Available. Here we have stored 12 values. In between these 12 Goals values, there are 2 duplicates. This formula doesn’t work for duplicates, it counts every value once. And keeps the rest of the duplicate’s places N/A.
Read More: Excel VBA to Sort Column Ascending (9 Examples)
1.2 Use SMALL & ROWS Functions Together
Secondly, another combination we can use for completing the task is the SMALL-ROWS combination.
The SMALL function returns numeric values based on their position in a list ranked by value.
=SMALL (array, n)array: A range of cells from which to extract the smallest values.
n: An integer that specifies the position from the smallest value.
Additionally, the ROWS function returns the count of rows in a given reference.
=ROWS (array)array: A reference to a cell or range of cells.
For example, if you insert A1:A3 within ROWS; ROWS(A1:A3), then it will return 3 since there are 3 rows within the range.
Let’s see the formula first.
=SMALL(array,ROWS(reference))Go through the steps given below to use these functions in your dataset.
Steps:
- To start with, insert the following formula in Cell E5.
=SMALL($C$5:$C$16,ROWS($C$5:C5))- After that, press Enter.
- Then, drag-down the Fill Handle tool to get the other values of goal in ascending order.
- Finally, you can see we have found the result we wanted. And there is no error as previously.
Read More: [Fixed!] Sort Largest to Smallest Not Working in Excel
1.3 Combination of AGGREGATE & ROWS Functions
Finally, we can use the AGGREGATE function. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.
=AGGREGATE (function_num, behavior_options, range)function_number: This number specifies which calculation should be made.
behavior_options: Set this using number. This number denotes how the function will behave.
range: Range you want to aggregate.
The AGGREGATE function does several tasks (a total of 19), so numbers of functions are predefined within it. We need to use 15 as our function_number.
Once you have chosen 15 (SMALL) as your function, then you need to add another parameter k after the range. So, the formula will be
=AGGREGATE(15,behavior_options,range,k)Here k is an integer that specifies the position from the lowest value.
There are 8 possible values for behavior_options.
Now, follow the steps given below to arrange numbers in ascending order using these functions for your dataset.
Steps:
- Firstly, select Cell E5 and insert the following formula.
=AGGREGATE(15,0,$C$5:$C$16,ROWS($C$5:C5))- Then, press Enter for Excel 365 or Ctrl + Shift + Enter to execute the formula for previous versions.
- Lastly, drag-down the Fill Handle tool to get the other values of goal in ascending order.
Read More: Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)
Similar Readings
- Excel VBA to Sort by Column Header Name (5 Easy Ways)
- Excel VBA to Sort a ComboBox List Alphabetically
- Excel VBA to Sort Alphabetically
- [Fixed!!] VBA Sort Not Working in Excel
- Excel VBA to Custom Sort (5 Easy Examples)
1.4 Arrange Rows Using SMALL & COLUMNS Functions
On some occasions, you may need to arrange rows. To show you an example, we have set the table in rows.
Now, we can use the SMALL function like earlier. But there is a change. We have used ROWS, but now we need to use COLUMNS.
The COLUMNS function returns the count of columns in a given reference.
=COLUMNS (array)array: A reference to a cell or range of cells.
So, now the formula will be
=SMALL(array,COLUMNS(reference))Here are the steps.
Steps:
- In the beginning, select Cell C7 and insert the following formula.
=SMALL($C$5:$H$5,COLUMNS($C$5:C5))- Then, press Enter.
- Further, drag-down the Fill Handle tool to get the other values of goal in ascending order.
You can use the AGGREGATE function similarly we have used earlier.
Read More: How to Sort Data by Value in Excel (5 Easy Methods)
2. Arrange Numbers in Ascending Order by Applying Built-in Excel Functions
If you have Excel 365, you will get the chance to use some of the built-in functions just to arrange numbers in ascending order in Excel using formula .
2.1 Insert SORT Function
The SORT function sorts the contents of a range or array in ascending or descending order.
=SORT (array, [sort_index], [sort_order], [by_col])array: The range, or array to sort
sort_index: A number indicating the row or column to sort by. This is an optional field. The default value is 1.
sort_order: A number indicating the desired sort order. 1 = Ascending, -1 = Descending. This is also an optional field. The default value is 1 (ascending).
by_col: A logical value indicating the desired sort direction. TRUE = sort by column. FALSE = sort by row. Default is FALSE.
Steps:
- Firstly, insert the following formula in Cell E5.
=SORT(B6:C18,2,1)
🔎 How Does the Formula Work?
- Here we have inserted the entire table (all the columns) as our array. In our earlier formulas, we have set only single-column, SORT allows multiple columns or rows within range.
- Then, Goals was the 2nd column within our range, we wanted to sort by that column, so we have inserted 2 here. This will arrange the number.
- Since the default sort_order is ascending, you will get it in ascending number order.
- However, you can use 1 in the sort_order field to arrange in ascending order.
- Then, press Enter.
- Thus, you will find the result you want.
Read More: Excel VBA to Sort in Descending Order (6 Examples)
2.2 Apply SORTBY Function
Lastly, another function we can use is the SORTBY function. It’s quite similar to the SORT function.
The SORTBY function sorts the contents of a range or array based on the values from another range or array.
=SORTBY (array, by_array, [sort_order], [array/order], ...)array: Range or array to sort
by_array: Range or array to sort by
sort_order: The order to use for sorting. 1 for ascending, -1 for descending. This is an optional field. Default is ascending.
array/order: Additional array and sort order pairs. This is also an optional field.
Here are the steps you will need to use this function.
Steps:
- Firstly, insert the following formula in Cell E5.
=SORT(B6:C18,2,1)
🔎 How Does the Formula Work?
- Similarly, to the previous method, we have selected the array. Our by_array was the Goals column.
- Earlier we have set the column number from our selection. Here in SORTBY, we have provided the column as a different array range.
- However, you may or may not use the sort_order while sorting in ascending order.
- Here, we have used 1 in the sort_order field. This also provided the numbers in ascending order.
- Secondly, press Enter.
- Thus, you will find the result you want.
Read More: Excel VBA to Sort Worksheets Numerically (5 Ideal Examples)
Practice Section
In the article, you will find an Excel workbook like the image given below to practice on your own.
Conclusion
That’s all for today. We have listed several approaches to arrange numbers in ascending order in Excel using formula. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know which of the formulas you are going to use. However, let us know any other approaches which might have been missed here.
Related Articles
- How to Sort Data in Excel Using Formula (2 Easy Methods)
- Sort Multiple Columns in Excel Independently of Each Other
- How to Auto Sort In Excel When Data Is Entered
- Sum Using OFFSET and MATCH in Excel (4 Ideal Examples)
- How to Sort Multiple Columns in Excel (5 Quick Approaches)
- Sort Two Columns to Match in Excel (2 Simple Methods)
- How to Sort by Column in Excel to Keep Rows Together
