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.
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 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))
- 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))
Follow the steps below to use these functions in your dataset.
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.
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.
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.
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.
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) .
- 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).
...
Syntax.
| Argument | Description |
|---|---|
| [sort_index] Optional | A number indicating the row or column to sort by |
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? ›- 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.
- 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.
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.
- 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.
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? ›- 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.
- 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.
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.
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? ›- 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.
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? ›
- 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.
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).