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.

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

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

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

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

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

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