# How to arrange numbers in ascending order using Excel formulas (2023)

Depending on various circumstances, you may need to do thisarrange numbers, including in a particularcommand. You can easily complete the task with multipleformulas. Today we will show you how to do itarrange numbersInascending orderin Excel mitFormula.

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

(Video) Basic Excel Tutorial - Sort a Column in Excel in Ascending or Descending Order

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 thetop scorerswith theirscoresabout the differentEuropean leagues. TheGoalscolumn contains thePay, like oursSortingorarrangewill be based on thatSplit.

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 wantsort byDieNumbervonscored goals. We introduced oneseparatedSplitTargets sorted. We aim to save thatscored goalsfrom the players inascending order.

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

In Excel, you may be familiar with thisINDEX-MATCHCombination. 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 theLine number, then thecol_numwill be optional.

Additionally,The MATCH functionis used to locate thePositionfrom aLookup-Wertin oneRow,Split, orTisch.

`=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 numbersINDEX-MATCHCombination. 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 itLine numberforINDEXuseTO FIT. Follow the steps below to arrange numbers in your dataset using these functions.

Steps:

• First selectCell E5and insert the following formula.

`=INDEX(\$C\$5:\$C\$16,MATCH(ROWS(\$C\$5:C5),COUNTIF(\$C\$5:\$C\$16,"<="&\$C\$5:\$C\$16),0))`

(Video) Excel 54 - Arrange data in Ascending or Descending order

• Then pressInput. However, if you are using previous versions insteadExcel 365, then you must useCtrl + Shift + Enterto executeFormulabecause it is aArray-Formel. • After that, pull thefill handleTool to get the other valuesGoalInascending order. 🔎How does the formula work?

• Here we inserted itGoalscolumn as theRow.
• On the one hand inTO FITfunction we usedROWS. With this function we producedincremental lines. This will be theLookup-WertforTO FIT.
• After that we used itCOUNTIFfunction to find thelookup_array. Within the function we have inserted theRowand thecriteria condition.
• COUNTIF(\$C\$5:\$C\$16,”<=”&\$C\$5:\$C\$16) comparesall values ​​in thesame rowmiteinander ureturnsaRowof theirrelative ranks.
• Then this array will be ourslookup_arrayhere inTO FITFunction.
• In addition, we used0ifmatch type.

Oh! We have met someerror values, although thePayhave beensorted. Do you understand why this is happening?

N / Astands forNot available. Here we saved12 values. between these12 goals values, there are2 Duplicates. This formulait does not workforDuplicates, Escountsany valueonce. And keeps the restDuplicatesputsN / A.

#### 1.2 Use SMALL & ROWS functions together

Secondly, another combination that we can use to complete the task is theSMALL ROWSCombination.

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 theROWSFunction returns the number of rows in a given reference.

`=ROWS (array)`

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

For example when insertingA1:A3withinROWS;REIHEN(A1:A3), then it returns3because there are3 rowswithin theArea.

Let's look at the formula first.

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

Steps:

• First, paste the following formula into CellE5.

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

• Then pressInput. • Then pull thefill handleTool to get the other valuesGoalInascending order.
• Finally you can see that we found themResultwe wanted. And there isno mistakeas before. Here, in the formula, we usedROWSto set theNforKLEINand set theGoals column.as theRow.

#### 1.3 Combination of AGGREGATE & ROWS functions

Finally we can usethe AGGREGATE function. TheAGGREGATE-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.

(Video) Excel Sort Column by Numbers in Ascending/Descending Order (2020)

Area:Area you want to aggregate.

TheAGGREGATEFunction handles multiple tasks (total19), so a number of functions are predefined in it. We must use15like ourfunction number.

Once you have chosen15 (KLEIN)as your function, then you need to add another parameterkby range. So the formula will be

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

Herekis aintegerthat specifies thePositionof thelowest value.

There are8Possible values ​​forbehavior options.

Now follow the steps given below to arrange numbers in ascending order using these functions for your dataset.

Steps:

• First selectCell E5and insert the following formula.

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

• Then pressInputforExcel 365orCtrl + Shift + Enterto executeFormulaforprevious versions. • Finally, pull the downfill handleTool to get the other valuesGoalInascending order. ImAGGREGATEfunction we used0in ourbehavior options.You can use any of the digits from 0 to 7 depending on your circumstances. Then we hired themArea, AndROWSthe same as before.

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

In some cases you may need to do thisarrange rows. To show you an example, we laid out the table in rows. Now we can use thoseKLEINwork like before. But there is a change. We usedROWS, but now we have to useCOLUMNS.

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 beginningCell C7and insert the following formula.

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

• Then pressInput. • Keep pulling downfill handleTool to get the other valuesGoalInascending order. This formula works just like the previous one. However, here we usedCOLUMNSfunction insteadROWSfunction and insertedColumn C.

You can use the...AGGREGATEwork similar to what we used before.

(Video) How to arrange numbers in ascending order in Excel 📊

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

If you haveExcel 365, you get the chance to use some of thembuilt-in functionsonly atarrange PayInascendingOrder in Excel withFormula. #### 2.1 Insert SORT function

The SORT function sortedthe content of aAreaorRowInascendingordescending 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 isINCORRECT.

Steps:

• First, paste the following formulaCell E5.

`=SORT(B6:C18,2,1)` 🔎How does the formula work?

• Here we inserted itwhole table(all columns) than oursRow. In our earlier formulas we only have single-column,SORT BYpermittedmultiple columnsorrowswithinArea.
• Then,Goalswas that2nd columnwithin ourArea, we wantedsort bythrough this column so we inserted2Here. This will arrange the number.
• Since the defaultsort orderIsascending, you get it inascendingorder of numbers.
• However, you can use1imsort orderarrange fieldascending order.
• Then pressInput.
• How to find the result you want. #### 2.2 Apply the SORT BY function

Finally is another function that we can usethe SORT BY function. It's pretty much like thatSORT BYFunction.

TheSORT BYfunctionsortedthe content of aAreaorRowbased on the values ​​ofanother areaorRow.

`=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 formulaCell E5.

`=SORT(B6:C18,2,1)` 🔎How does the formula work?

• Similar to the previous method, we selected the array. Ourfrom_arraywas thatGoalsSplit.
• We used to set themcolumn numberfrom our selection. In hereSORT BY,We specified the column differentlyarray area.
• However, you can use them or notsort orderwhen sortingascendingCommand.
• Here we used1imsort orderField. This also provided thePayInascendingCommand.
• Second pressInput.
• How to find the result you want. (Video) How to Sort Excel Data in Ascending or Descending Order

## 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 approachesarrange numbersInascending orderim Excel-Formatuse 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.

• 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?
1. Select a single cell in the column you want to sort.
2. On the Data tab, in the Sort & Filter group, click. to perform an ascending sort (from A to Z, or smallest number to largest).
3. Click. to perform a descending sort (from Z to A, or largest number to smallest).

How do I sort data in Excel using formulas? ›

In the example above, the source array for our SORT formula is range A5:D20. The SORT function will return an array, which will spill if it's the final result of a formula.
...
Syntax.
ArgumentDescription
[sort_index] OptionalA number indicating the row or column to sort by
3 more rows

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
1. Select Custom Sort.
3. 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. ...
4. For Sort On, select Values.
5. 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
1. Select a cell in the column you want to sort by. ...
2. Click the Data tab, then select the Sort command.
3. The Sort dialog box will appear. ...
5. Select the next column you want to sort by, then click OK. ...
6. 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? ›

1. Highlight the rows and/or columns you want sorted. ...
2. Navigate to 'Data' along the top and select 'Sort. ...
3. If sorting by column, select the column you want to order your sheet by. ...
4. If sorting by row, click 'Options' and select 'Sort left to right. ...
5. Choose what you'd like sorted. ...
6. Choose how you'd like to order your sheet.
Aug 9, 2018

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

You can sort columns of cells alphabetically and numerically.
2. To select a column, tap a letter at the top.
3. To open the menu, tap the top of the column again.
4. Tap More .
5. 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
1. Select the column that you want to be sorted last (in this case, select the Sales data first – C1:C13).
2. Click on the Data tab.
3. In the Sort and Filter group, click on the Z to A sorting icon. ...
4. The above step would make a Sort Warning dialog box pop-up. ...
5. Click Sort.

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

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

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
1. Tap the table, then tap the letter above the column with the data you want to sort by.
2. 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
1. Select a cell within the data.
2. Select Home > Sort & Filter. Or, select Data > Sort.
3. 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).

## Videos

1. How to Sort Ascending Numerically in Excel : MS Excel Tips
(eHowTech)
2. Sort multiple data in descending or ascending order in Excel
(Dr. Mahmoud Omar (Statistics))
3. Excel Automatically Sort When Data Changes or Added | Auto Sort Excel Formula | Auto Sort Macro
(Chester Tugwell)
4. Excel in Ascending or Descending || Arrange the Number in their Ascending Order ||Sorting|| Part-08
(RB Real Tech)
5. MS Excel: How to Sort Value with Data (Large to Small & Ascending to Descending)
(MJ Tube)
6. MS Excel Rank Function - Ascending and Descending Order
(Dike Chris)
Top Articles
Latest Posts
Article information

Author: Saturnina Altenwerth DVM

Last Updated: 12/25/2022

Views: 6252

Rating: 4.3 / 5 (64 voted)

Author information

Name: Saturnina Altenwerth DVM

Birthday: 1992-08-21

Address: Apt. 237 662 Haag Mills, East Verenaport, MO 57071-5493

Phone: +331850833384

Job: District Real-Estate Architect

Hobby: Skateboarding, Taxidermy, Air sports, Painting, Knife making, Letterboxing, Inline skating

Introduction: My name is Saturnina Altenwerth DVM, I am a witty, perfect, combative, beautiful, determined, fancy, determined person who loves writing and wants to share my knowledge and understanding with you.