Depending on various circumstances, you may need to do this**arrange numbers**, including in a particular**command**. You can easily complete the task with multiple**formulas**. Today we will show you how to do it**arrange numbers**In**ascending order**in Excel mit**Formula**.

**Table of contents** hide

Download exercise book

2 ways to arrange numbers in ascending order with Excel formulas

1. Use combination of different Excel functions to arrange numbers in ascending order

2. Arrange numbers in ascending order by applying built-in Excel functions

2.1 Insert SORT function

2.2 Apply the SORT BY function

practice section

Diploma

More reading

## Download exercise book

You are welcome to download the exercise booklet from the link below.

Arrange numbers in ascending order.xlsx

## 2 ways to arrange numbers in ascending order with Excel formulas

Before starting the tutorial, it's a good idea to familiarize yourself with the workbook that forms the basis of our examples.

We have a simple table containing the**top scorers**with their**scores**about the different**European leagues**. The**Goals**column contains the**Pay**, like ours**Sorting**or**arrange**will be based on that**Split**.

Note that this is a simple table with a simple scenario. In real cases, you may encounter a much larger and more complex dataset.

### 1. Use combination of different Excel functions to arrange numbers in ascending order

You can see that in our selected base dataset we have a single column for numbers;**Goals**.

Let's imagine a scenario where we want**sort by**Die**Number**von**scored goals**.

We introduced one**separated**Split**Targets sorted**. We aim to save that**scored goals**from the players in**ascending order**.

#### 1.1 Combination of INDEX, MATCH, ROWS & COUNTIF functions

In Excel, you may be familiar with this**INDEX-MATCH**Combination. Yes, it is one of the most powerful and widely used combo in Excel. This combination is suitable for our task”**Arrange the numbers in ascending order using the formula**“. Let's see how that goes.

First,The INDEX functionreturns the value at a specific position in a range or array.

`=INDEX (array, row_num, [col_num], [area_num])`

**Array:**A range of cells or an array constant.

**Line number:**The row position in the reference or array.

**Column number:**The column position in the reference or array.

**area_num**:The reference range to use. This is an optional field.

If you use the**Line number**, then the**col_num**will be optional.

Additionally,The MATCH functionis used to locate the**Position**from a**Lookup-Wert**in one**Row**,**Split**, or**Tisch**.

`=MATCH (search value, search array, [match type])`

**Lookup-Wert**:The value in lookup_array to match.

**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 smaller (default), 0 = exact match, -1 = exact or next largest.

With this we can organize the numbers**INDEX-MATCH**Combination. However, we will use theROWSand theCOUNTIFwork too. Let's look at the generic formula.

`=INDEX(array, MATCH(ROWS(start_row),COUNTIF(array,criteria),0))`

Here we have set it**Line number**for**INDEX**use**TO FIT**. Follow the steps below to arrange numbers in your dataset using these functions.

**Steps:**

- First 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
**Input**. However, if you are using previous versions instead**Excel 365**, then you must use**Ctrl + Shift + Enter**to execute**Formula**because it is a**Array-Formel**.

- After that, pull the
**fill handle**Tool to get the other values**Goal**In**ascending order**.

🔎**How does the formula work?**

- Here we inserted it
**Goals**column as the**Row**. - On the one hand in
**TO FIT**function we used**ROWS**. With this function we produced**incremental lines**. This will be the**Lookup-Wert**for**TO FIT**. - After that we used it
**COUNTIF**function to find the**lookup_array**. Within the function we have inserted the**Row**and the**criteria condition**. **COUNTIF($C$5:$C$16,”<=”&$C$5:$C$16)****compares**all values in the**same row**miteinander u**returns**a**Row**of their**relative ranks**.- Then this array will be ours
**lookup_array**here in**TO FIT**Function. - In addition, we used
**0**if**match type**.

Oh! We have met some**error values**, although the**Pay**have been**sorted**. Do you understand why this is happening?

**N / A**stands for**Not available**. Here we saved**12 values**. between these**12 goals values**, there are**2 Duplicates**. This formula**it does not work**for**Duplicates**, Es**counts**any value**once**. And keeps the rest**Duplicates**puts**N / A**.

#### 1.2 Use SMALL & ROWS functions together

Secondly, another combination that we can use to complete the task is the**SMALL ROWS**Combination.

The SMALL functionreturns numeric values based on their position in a value-ordered list.

`=SMALL(Array, n)`

**Array:**A range of cells from which the smallest values are extracted.

**N:**An integer that specifies the position from the smallest value.

Additionally the**ROWS**Function returns the number of rows in a given reference.

`=ROWS (array)`

**Array:**A reference to a cell or range of cells.

For example when inserting**A1:A3**within**ROWS**;**REIHEN(A1:A3)**, then it returns**3**because there are**3 rows**within the**Area**.

Let's look at the formula first.

`=SMALL(array,ROWS(reference))`

Follow the steps below to use these functions in your dataset.

**Steps:**

- First, paste the following formula into Cell
**E5**.

`=SMALL($C$5:$C$16,ROWS($C$5:C5))`

- Then press
**Input**.

- Then pull the
**fill handle**Tool to get the other values**Goal**In**ascending order**. - Finally you can see that we found them
**Result**we wanted. And there is**no mistake**as before.

Here, in the formula, we used**ROWS**to set the**N**for**KLEIN**and set the**Goals column.**as the**Row**.

#### 1.3 Combination of AGGREGATE & ROWS functions

Finally we can usethe AGGREGATE function. The**AGGREGATE**-Function can apply various aggregate functions to a list or database, with the option to ignore hidden rows and error values.

`=AGGREGATE(function number, behavior options, range)`

**Function number:**This number indicates which calculation is to be performed.

**Behavior Options:**Set this with the number. This number indicates how the function will behave.

**Area**:Area you want to aggregate.

The**AGGREGATE**Function handles multiple tasks (total**19**), so a number of functions are predefined in it. We must use**15**like our**function number***.*

Once you have chosen**15 (KLEIN)**as your function, then you need to add another parameter**k**by range. So the formula will be

`=AGGREGATE(15,behavioroptions,range,k)`

Here**k**is a**integer**that specifies the**Position**of 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:**

- First select
**Cell E5**and insert the following formula.

`=AGGREGATE(15,0,$C$5:$C$16,ROWS($C$5:C5))`

- Then press
**Input**for**Excel 365**or**Ctrl + Shift + Enter**to execute**Formula**for**previous versions.**

- Finally, pull the down
**fill handle**Tool to get the other values**Goal**In**ascending order**.

Im**AGGREGATE**function we used**0**in our**behavior options***.*You can use any of the digits from 0 to 7 depending on your circumstances. Then we hired them**Area**, And**ROWS**the same as before.

#### 1.4 Arrange rows using SMALL & COLUMNS functions

In some cases you may need to do this**arrange rows**. To show you an example, we laid out the table in rows.

Now we can use those**KLEIN**work like before. But there is a change. We used**ROWS**, but now we have to use**COLUMNS**.

The COLUMNS functionreturns the number 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(Referenz))`

Here are the steps.

**Steps:**

- Choose at the beginning
**Cell C7**and insert the following formula.

`=SMALL($C$5:$H$5,COLUMNS($C$5:C5))`

- Then press
**Input**.

- Keep pulling down
**fill handle**Tool to get the other values**Goal**In**ascending order**.

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

You can use the...**AGGREGATE**work similar to what we used before.

### 2. Arrange numbers in ascending order by applying built-in Excel functions

If you have**Excel 365**, you get the chance to use some of them**built-in functions**only at**arrange** **Pay**In**ascending**Order in Excel with**Formula**.

#### 2.1 Insert SORT function

The SORT function **sorted**the content of a**Area**or**Row**In**ascending**or**descending order**.

`=SORT(array, [sort_index], [sort_order], [by_col])`

**Array:** The range or array to sort

**sort_index:**A number that specifies the row or column to sort by. This is an optional field. The default value is 1.

**Sort order:**A number that specifies the desired sort order.**1 = Ascending**,**-1 = Descending**. This is also an optional field. The default is 1 (ascending).

**from_col:**A logical value that indicates the desired sort direction.**TRUE**= sort by column.**INCORRECT**= sort by line. default is**INCORRECT**.

**Steps:**

- First, paste the following formula
**Cell E5**.

`=SORT(B6:C18,2,1)`

🔎**How does the formula work?**

- Here we inserted it
**whole table**(all columns) than ours**Row**. In our earlier formulas we only have single-column,**SORT BY**permitted**multiple columns**or**rows**within**Area**. - Then,
**Goals**was that**2nd column**within our**Area**, we wanted**sort by**through this column so we inserted**2**Here. This will arrange the number. - Since the default
**sort order**Is**ascending**, you get it in**ascending**order of numbers. - However, you can use
**1**im**sort order**arrange field**ascending order**.

- Then press
**Input**. - How to find the result you want.

#### 2.2 Apply the SORT BY function

Finally is another function that we can use**the SORT BY function**. It's pretty much like that**SORT BY**Function.

The**SORT BY**function**sorted**the content of a**Area**or**Row**based on the values of**another area**or**Row**.

`=SORT BY (array, by_array, [sort_order], [array/order], ...)`

**Array:**Range or array to sort

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

**Arrangement/Order:**Additional array and sort order pairs. This is also an optional field.

Here are the steps you need to use this feature.

**Steps:**

- First, paste the following formula
**Cell E5**.

`=SORT(B6:C18,2,1)`

🔎**How does the formula work?**

- Similar to the previous method, we selected the array. Our
**from_array**was that**Goals**Split. - We used to set them
**column number**from our selection. In here**SORT BY,**We specified the column differently**array area**. - However, you can use them or not
**sort order**when sorting**ascending**Command. - Here we used
**1**im**sort order**Field. This also provided the**Pay**In**ascending**Command.

- Second press
**Input**. - How to find the result you want.

## practice section

In the article, you can find an Excel workbook like the image given below to practice by yourself.

## Diploma

That is all for today. We have listed several approaches**arrange numbers**In**ascending order**im Excel-Format**use formula**. I hope you find this helpful. Feel free to comment if something seems difficult to understand. Tell us which of the formulas you will use. However, let us know of any other approaches that may have been overlooked here.

## More reading

**How to sort data in Excel using a formula****How to sort multiple columns independently in Excel****Automatic Sorting When Entering Data in Excel (3 Methods)****Sum with 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 by match (both exact match and partial match)****Sort columns in Excel and keep rows together**

## FAQs

### What is the formula to arrange numbers in ascending order in Excel? ›

The formula, **=SORT(B15:F23, 2, 1, FALSE)** translates in English to: Sort the data in cells B15:F23, based on the second column, in ascending order, sorted by row.

**How do I automatically sort in ascending order in Excel? ›**

Auto sort with Formula in Excel

To auto sort with formula, we can use the formula **=LARGE(B4:B14)** .

**How do you use ascending formula in Excel? ›**

**How to sort in Excel?**

- Select a single cell in the column you want to sort.
- On the Data tab, in the Sort & Filter group, click. to perform an ascending sort (from A to Z, or smallest number to largest).
- Click. to perform a descending sort (from Z to A, or largest number to smallest).

**How do I sort data in Excel using formulas? ›**

...

Syntax.

Argument | Description |
---|---|

[sort_index] Optional | A number indicating the row or column to sort by |

**Is there a sort function in Excel? ›**

The SORT function in Excel sorts the contents of an array or range by columns or rows, in ascending or descending order. SORT belongs to the group of Dynamic array functions.

**How can I arrange numbers in ascending order? ›**

Ascending order is a method of arranging numbers **from smallest value to largest value**. The order goes from left to right. Ascending order is also sometimes named as increasing order. For example, a set of natural numbers are in ascending order, such as 1 < 2 < 3 < 4 < 5 < 6 < 7 < 8… and so on.

**What is the formula to sort descending in Excel? ›**

To sort the numbers in ascending or descending order, we use the formulas “LARGE”, “SMALL” and “ROW” in Microsoft Excel. To sort in ascending order use the “SMALL” function along with the “ROW” function. And to sort in descending order **use the “LARGE” function along with the “ROW” function**.

**How do I sort multiple columns in Excel using formulas? ›**

**Sort data in a table**

- Select Custom Sort.
- Select Add Level.
- For Column, select the column you want to Sort by from the drop-down, and then select the second column you Then by want to sort. ...
- For Sort On, select Values.
- For Order, select an option, like A to Z, Smallest to Largest, or Largest to Smallest.

**How do you SORT a column using formulas? ›**

**Sorting levels**

- Select a cell in the column you want to sort by. ...
- Click the Data tab, then select the Sort command.
- The Sort dialog box will appear. ...
- Click Add Level to add another column to sort by.
- Select the next column you want to sort by, then click OK. ...
- The worksheet will be sorted according to the selected order.

**How do you use the order function in Excel? ›**

Select a cell in the column you want to sort. On the Data tab, in the Sort & Filter group, click Sort. In the Sort dialog box, under Column, in the Sort by or Then by box, select the column that you want to sort by a custom list. Under Order, select Custom List.

### How do I rearrange data in Excel? ›

- Highlight the rows and/or columns you want sorted. ...
- Navigate to 'Data' along the top and select 'Sort. ...
- If sorting by column, select the column you want to order your sheet by. ...
- If sorting by row, click 'Options' and select 'Sort left to right. ...
- Choose what you'd like sorted. ...
- Choose how you'd like to order your sheet.

**How do you arrange data in ascending order in sheets? ›**

**You can sort columns of cells alphabetically and numerically.**

- On your Android phone or tablet, open a spreadsheet in the Google Sheets app.
- To select a column, tap a letter at the top.
- To open the menu, tap the top of the column again.
- Tap More .
- Scroll down and tap SORT A-Z or SORT Z-A. Your data will be sorted.

**Which function is used to sort in descending order? ›**

**DESC**. The DESC command is used to sort the data returned in descending order.

**Which is the best method to sort a data set by multiple columns? ›**

**Multi-Level Sorting Using Sort Icons**

- Select the column that you want to be sorted last (in this case, select the Sales data first – C1:C13).
- Click on the Data tab.
- In the Sort and Filter group, click on the Z to A sorting icon. ...
- The above step would make a Sort Warning dialog box pop-up. ...
- Click Sort.

**How do I sort multiple columns in ascending order in Excel? ›**

**Sort by several columns**

- Click the Sort button on the Data tab or Custom Sort on the Home tab to open the Sort dialog.
- Then click the Add Level button as many times as many columns you want to use for sorting:
- From the "Sort by" and "Then by" dropdown lists, select the columns by which you want to sort your data.

**How do you sort A to Z in Excel and keep rows together? ›**

Alphabetize and keep rows together

If your data set contains two or more columns, you can **use the A-Z or Z-A button to put one of the columns in alphabetical order** and Excel will automatically move the data in other columns, keeping the rows intact.

**How do you arrange a set of numbers in ascending order? ›**

Ascending order is a method of arranging numbers **from smallest value to largest value**. The order goes from left to right. Ascending order is also sometimes named as increasing order. For example, a set of natural numbers are in ascending order, such as 1 < 2 < 3 < 4 < 5 < 6 < 7 < 8… and so on.

**How do you arrange numbers in ascending order in numbers? ›**

**Sort data in a column**

- Tap the table, then tap the letter above the column with the data you want to sort by.
- Tap. at the bottom of the screen, then tap an option: Sort Ascending: Sorts the table rows in alphabetical order (A to Z) or by increasing numerical values based on the data in the selected column.

**How do I arrange numbers in ascending? ›**

The number with the least number of digits is the smallest. Write it first. Continue this till all the numbers left for comparison have the same number of digits. For the numbers having the same number of digits, start with comparing the numbers from the leftmost digit.

**How do you arrange numbers from highest to lowest? ›**

Numbers are said to be in **descending order** when they are arranged from the largest to the smallest number. E.g. 25, 21, 17, 13 and 9 are arranged in descending order.

### How do you rearrange data in ascending or descending order? ›

**Sort data in a table**

- Select a cell within the data.
- Select Home > Sort & Filter. Or, select Data > Sort.
- Select an option: Sort A to Z - sorts the selected column in an ascending order. Sort Z to A - sorts the selected column in a descending order.

**Can Excel sort numbers from highest to lowest? ›**

Sort numbers

Select a cell in the column you want to sort. On the Data tab, in the Sort & Filter group, do one of the following: **To sort from low to high, click**. **(Sort Smallest to Largest)**.