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.

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.

**Table of Contents**hide

## 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. This combination is suitable for our task “arrange numbers in ascending order using formula”. Let’s see how to do that.

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.

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.

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

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.

`INDEX(array, MATCH(ROWS(starting_row),COUNTIF(array,criteria),0))`

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,"<="&$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.

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

Visit the Microsoft Support site to know more about **SMALL. **

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.

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

Let’s see the formula first.

`SMALL(array,ROWS(reference))`

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.

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

Function | Function_number |
---|---|

AVERAGE | 1 |

COUNT | 2 |

COUNTA | 3 |

MAX | 4 |

MIN | 5 |

PRODUCT | 6 |

SUM | 9 |

LARGE | 14 |

SMALL | 15 |

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

`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. They are

Value | Behavior |
---|---|

0 | Ignore SUBTOTAL and AGGREGATE functions |

1 | Ignore hidden rows, SUBTOTAL and AGGREGATE functions |

2 | Ignore error values, SUBTOTAL and AGGREGATE functions |

3 | Ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions |

4 | Ignore nothing |

5 | Ignore hidden rows |

6 | Ignore error values |

7 | Ignore hidden rows and error values |

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.

`COLUMNS (array)`

**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

**SMALL(array,COLUMNS(reference))**

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 Numbers in Ascending Order

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.

`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**

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.

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

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.

## Further Readings

**How to Sort Data in Excel Using Formula****How to Sort Multiple Columns in Excel Independently of Each Other****Auto Sort When Data is Entered in Excel (3 Methods)****Sum Using OFFSET and MATCH in Excel (With Alternative Options)****How to Sort Multiple Columns in Excel (5 Quick Approaches)****How to Sort Two Columns in Excel to Match (Both Exact and Partial Match)****Sorting Columns in Excel While Keeping Rows Together**