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

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 is containing 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 complex data set.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Arrange Numbers in Ascending Order using Formula

1. Combination of Different Excel Functions

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.

I. INDEX-MATCH Combination

In Excel, you may be familiar with the INDEX-MATCH combination. Yes, it is one of the powerful and widely used combinations in Excel.

The INDEX function returns the value at a given location in a range or array.

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.

Explore the Microsoft Support site to know more about INDEX. 

The MATCH function is used to locate the position of a lookup value in a row, column, or table.

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.

Explore the Microsoft Support site to know more about MATCH. 

We can arrange the numbers using this INDEX-MATCH combination. We will use the ROWS and the COUNTIF function as well. Let’s see the generic formula.

Here we have set the row_num for INDEX using MATCH.  

Write the formula in Excel.

You can see we have found the least number at the beginning.

Here we have inserted the Goals column as the array. In the MATCH function, we have used ROWS. Using this function, we have produced incremental rows. This will be the lookup_value for MATCH. 

We have used the COUNTIF function to find the lookup_array. Within the function, we have inserted the array and the criteria condition.

COUNTIF($E$4:$E$16," compares all the values in the same row with each other and returns an array of their relative ranks.

Write this portion in Excel.

Press the F9 key to see the inner mechanism.

You will see their relative ranks. This array will be our lookup_array here in MATCH

We have used 0 as match_type. Don’t forget we need to use CTRL + SHIFT + ENTER to execute the formula since it is an array formula.

Use the Excel AutoFill feature to find all the numbers rearranged in ascending order.

Oh! We have encountered a few error values, though the numbers have been sorted. Do you understand why did this happen?

N/A stands for Not Available. Here we have stored 13 values. In between these 13 Goals values, there are 3 duplicates. This formula doesn’t work for duplicates, it counts every value once. And keeps the rest of the duplicate’s places N/A. 

II. SMALL-ROWS Combination

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.

array: A range of cells from which to extract the smallest values.

n: An integer that specifies the position from the smallest value.

Visit the Microsoft Support site to know more about SMALL. 

The ROWS function returns the count of rows in a given reference.

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.

Visit the Microsoft Support site to know more about the function.

Let’s see the formula first.

We have used ROWS to set the n for SMALL.

Write the formula in Excel.

Inside the SMALL function, we have set the Goals column as the array. ROWS returns n. 

Do you understand what ROWS($E$4:E4) returns? It will return 1 since the only 1 row in the range. And our formula such a way that the lowest value will come as result.

Keeping the absolute reference unchanged, change the latter cell reference to produce another value.

We have found 18, which is the lowest among this column. Exercise AutoFill will give you the numbers arranged in ascending order.

You can see we have found the result we wanted. And there is no error as previously.

III. AGGREGATE-ROWS Combination

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.

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 are listing few frequently used function numbers

From this table, you have understood that 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

Here k is an integer that specifies the position from the lowest value.

There are 8 possible values for behavior_options. They are

We are leaving the link to the Microsoft Support site for the AGGREGATE function, which will help you know the function much deeper.

Let’s write the formula in Excel.

We have used 0 in our behavior_options. You can use any of the digits from 0 to 7 depending on your circumstances.

We have set the range, and ROWS the same as earlier. We found the result we have desired.

Exercise Excel AutoFill for the rest of the rows.

We have found the numbers in ascending order.

Arrange Rows (Bonus)

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.

array: A reference to a cell or range of cells.

Visit the Microsoft Support site to know more about the function.

So, now the formula will be

Write the formula in Excel.

Use the Excel AutoFill feature for the rest of the columns.

You can use the AGGREGATE function similarly we have used earlier.

2. Built-in Excel Function to Arrange

If you have Excel 365, you will get the chance to use some of the built-in functions just to do the sort.

I. SORT Function

The SORT function sorts the contents of a range or array in ascending or descending order.

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

Explore the Microsoft Support site to know more about the function.

Make use of this function in Excel.

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.

Goals was the 4th column within our range, we wanted to sort by that column, so we have inserted 4 here. This will arrange the number.

Since the default sort_order is ascending, we have found it in ascending number order.

You can use 1 in the sort_order field to arrange in ascending order. (See the image below)

We have found the result we wanted.

II. SORTBY Function

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.

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.

Explore the Microsoft Support site to know more about the function.

Use the function in Excel.

Similar to the previous, 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.

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

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. Let us know any other approaches which might have been missed here.

The post How to Arrange Numbers in Ascending Order in Excel using Formula appeared first on ExcelDemy.