How to Arrange Numbers in Ascending Order with Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

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.

Ways to Arrange Numbers in Ascending Order in Excel Using Formula

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.

Use Combination of Different Excel Functions to Arrange Numbers in Ascending Order

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.

Use Combination of INDEX, MATCH, ROWS & COUNTIF Functions to Arrange Numbers in Ascending Order in Excel Using 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.

Use SMALL & ROWS Functions Together to Arrange Numbers in Ascending Order in Excel Using Formula

  • 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.

Here, in the formula, we have used ROWS to set the n for SMALL and set the Goals column as the array.

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.

Use Combination of AGGREGATE & ROWS Functions to Arrange Numbers in Ascending Order in Excel Using Formula

  • Lastly, drag-down the Fill Handle tool to get the other values of goal in ascending order.

In the AGGREGATE function, we have used 0 in our behavior_options. You can use any of the digits from 0 to 7 depending on your circumstances. Then, we have set the range, and ROWS the same as earlier.

Read More: Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)


Similar Readings


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.

Arrange Rows Using SMALL & COLUMNS Functions in Ascending Order in Excel Using Formula

  • Further, drag-down the Fill Handle tool to get the other values of goal in ascending order.

This formula works exactly like the previous one. However, here we used COLUMNS function instead of ROWS function and inserted Column C.

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 .

Arrange Numbers in Ascending Order by Applying Built-in Excel Functions


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)

Insert SORT Function to Arrange Numbers in Ascending Order in Excel Using Formula

🔎 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.

How to Arrange Numbers in Ascending Order in Excel Using Formula

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)

Apply SORTBY Function to Arrange Numbers in Ascending Order in Excel Using Formula

🔎 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.

Practice Section


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

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo