Shamima Sultana
To do analysis or to keep numbers in order, Sorting is important. In Excel, there are a couple of ways to Sort numbers. There are two possible ways to Sort numbers: one is Smallest to Largest, and another is vice versa. In this article, I’m going to explain how to perform Excel Sort numbers.
To make the explanation understandable, I’m going to use a sample dataset that contains the salary information of a particular employee. The data has 3 columns; these are Employee Name, Region, and Salary.
Download to Practice
8 Ways to Sort Numbers in Excel
1. Sort Numbers from Smallest to Largest in Excel
You can Sort the numbers from Smallest to Largest using the ribbon feature.
Let me demonstrate to you the procedure,
To begin with, select the cell range that contains numbers.
➤ I selected the cell range D4:D13.
Open the Home tab >> go to Editing >> from Sort & Filter >> select Sort Smallest to Largest
A dialog box will pop up. From there select What do you want to do?
In What do you want to do? There have two options one is “Expand the selection” another is “Continue with the current solution”.
➤ Expand the selection means it will sort the numbers along with their adjacent cell values which will maintain the relationship between cells.
➤ Continue with the current solution means it will only sort the numbers the adjacent values won’t change it will remain as it is. The problem is it will change the number’s position for that reason it may be lost its relation to its adjacent cell.
⏩ As I want to maintain the relationship, I selected the option Expand the selection.
Then, click on Sort.
Hence, it will Sort the numbers in Smallest to Largest order along with the adjacent cell values.
Read More: How to Sort Excel Tabs in Ascending or Descending Order (2 Ways)
2. Sort Numbers from Largest to Smallest in Excel
If you want, you can Sort the numbers from Largest to Smallest using the ribbon feature.
To start with, select the cell range that contains numbers.
➤ I selected the cell range D4:D13.
Open the Home tab >> go to Editing >> from Sort & Filter >> select Sort Largest to Smallest
A dialog box will pop up. From there select What do you want to do?
⏩ I selected the option Expand the selection.
Then, click on Sort.
Therefore, it will Sort the numbers in Largest to Smallest order along with the adjacent cell values.
Read More: How to Sort Array with Excel VBA (Both Ascending and Descending Order)
3. Sort Numbers Based on Criteria in Excel
In case you want to Sort numbers based on criteria you can do it by using the Custom Sort feature.
To begin with, select the cell range that contains numbers.
➤ I selected the cell range D4:D13.
Open the Home tab >> go to Editing >> from Sort & Filter >> select Custom Sort
A dialog box will pop up. From there select What do you want to do?
⏩ I selected the option Expand the selection.
Then, click on Sort.
Another dialog box will pop up. From there click on Add Level
In Sort by select the Column name based on which you want to Sort your numbers.
In Then by select the Column which contains the numbers.
⏩ I selected the Region column in Sort by and in Order selected A to Z.
⏩ I selected the Salary($) column in Then by and in Order selected Smallest to Largest.
Then, click on OK.
As a result, it will Sort the numbers based on the Region column with the order Smallest to Largest. Here, each Region will contain the smallest and largest values.
If you want, you can Sort numbers based on criteria from Largest to Smallest. To do so follow the steps I explained earlier.
⏩ I selected the Region column in Sort by and in Order selected A to Z.
⏩ I selected the Salary($) column in Then by and in Order selected Largest to Smallest.
Then, click on OK.
Hence, it will Sort the numbers based on the Region column with the order Largest to Smallest. Here, each Region will contain the largest and the smallest values.
If you want, you can use the Sort command from the Data tab to use the Custom Sort.
Open Data tab >> select Sort it will open the dialog box to apply the custom sort.
Read More: How to Arrange Numbers in Ascending Order in Excel Using Formula
4. Using Excel Formula to Sort Numbers in Ascending Order
If you only want to Sort numbers in an Ascending (Smallest to Largest) order, then you can use the SMALL function and the ROWS function together.
Here, If you want you can use the entire number range with absolute reference or you can name the cell range of numbers you are going to use.
I named the range D4:D13 as data_1.
To begin with, select any cell to place the resultant value.
➤ I selected cell F4.
⏩ In cell F4 type the following formula.
=SMALL(data_1,ROWS($D$4:D4))
Here, in the SMALL function, I used the named range data_1 as an array and ROWS($D$4:D4) as k (it is the position in a range of data that starts from the smallest).
Then, in the ROWS function, I selected the range $D$4:D4 as an array that will return the position. Here, I used absolute reference so that I can use it for the remaining cells.
Now, the SMALL function will extract the Smallest number from the given range of data.
Press ENTER then you will get the Smallest number from the used named range.
⏩ You can use the Fill Handle to AutoFill the formula for the rest of the cells.
Here, all the numbers are Sorted in Ascending order.
Read More: Excel Sort Dates in Chronological Order (6 Effective Ways)
Similar Readings
- Sorting Columns in Excel While Keeping Rows Together
- How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)
- Auto Sort When Data is Entered in Excel (3 Methods)
- How to Sort Two Columns in Excel to Match (Both Exact and Partial Match)
- Sort Dates in Excel by Year (4 Easy Ways)
5. Using Excel Formula to Sort Numbers in Descending Order
Similar to the previous section, we can use the LARGE function and the ROWS function to Sort the numbers, but this time the functions will sort in Descending order.
Here, I named the range D4:D13 as data_2.
To start with, select any cell to place the resultant value.
➤ I selected cell F4.
⏩ In cell F4 type the following formula.
=LARGE(data_2,ROWS($D$4:D4))
Here, in the LARGE function, I used the named range data_2 as an array and ROWS($D$4:D4) as k (it is the position in a range of data that starts from the largest).
Then, in the ROWS function, I selected the range $D$4:D4 as an array that will return the position.
Now, the LARGE function will extract the Largest number from the given range of data.
Press ENTER then you will get the Largest number from the used named range.
⏩ You can use the Fill Handle to AutoFill the formula for the rest of the cells.
Here, all the numbers are Sorted in Descending order.
Read More: How to Use Excel Shortcut to Sort Data (7 Easy Ways)
6. Sort Numbers Using Excel Context Menu
If you want, you can Sort numbers using the context menu.
To demonstrate the procedure, I’m going to use the dataset given below where I added two columns one is Tax to Pay, and another is Tax Percentage to show you how different Number Formats works while Sorting.
To begin with, select the cell range from which you want to Sort the numbers.
➤ I selected the cell range F4:F13.
Now, right click on the mouse then from Sort >> select Sort Largest to Smallest
A dialog box will pop up. From there select What do you want to do?
⏩ I selected the option Expand the selection.
Then, click on Sort.
Hence, it will Sort the numbers in Largest to Smallest order along with the adjacent cells’ values.
Read More: How to Sort and Filter Data in Excel (A Complete Guideline)
7. Using A→Z Command to Sort Numbers Smallest to Largest
From the Data tab, you also can Sort numbers using the sorting command A→Z. It will sort the numbers from Smallest to Largest.
To start with, select the cell range that contains numbers.
➤ I selected the cell range D4:D13.
Open the Data tab >> select A→Z
A dialog box will pop up. From there select What do you want to do?
⏩ I selected the option Expand the selection.
Then, click on Sort.
Hence, it will Sort the numbers in Smallest to Largest order along with the adjacent cell values.
Read More: How to Put Numbers in Numerical Order in Excel (6 Methods)
8. Using Z→A Command to Sort Numbers Largest to Smallest
By using the Z→A command you can Sort the numbers from Largest to Smallest.
To start with, select the cell range that contains numbers.
➤ I selected the cell range D4:D13.
Open the Data tab >> select Z→A
A dialog box will pop up. From there select What do you want to do?
⏩ I selected the option Expand the selection.
Then, click on Sort.
Therefore, it will Sort the numbers in Largest to Smallest order along with the adjacent cell values.
Read More: Advantages of Sorting Data in Excel (All Features Included)
Things to Remember
🔺 While using Custom Sorting remember to check the My data has headers option.
🔺 If you don’t want to change shuffle your data then you will need to select the Expand the selection option.
Practice Section
I’ve provided a practice sheet to practice the explained methods.
Conclusion
In this article, I have shown 8 ways Excel Sort numbers. These ways will help you to remove Sort numbers easily based on your requirements. Feel free to comment down below for any types of queries and suggestions.
arrangement a 4 digit number in a row from lowest to highest.
What`s the formula ?
Dear SATHI,
Thank you very much for reading this article. Here, you have a query to arrange a 4-digit number in a row from the lowest to highest using a formula. We have found a solution for your mentioned problem in the below section.
● Look at the below image.
A four-digit number is inserted in the dataset.
● First, we will separate the digits of the inserted number using the following formula.
=MID($B5,COLUMN()-(COLUMN($E4)- 1),1)
We use the combination of the MID and COLUMN function.
Formula Explanation:
● COLUMN($E4)
This will return the column number of the reference.
Result: 5
● COLUMN($E4)-1
We will subtract 1 from the column number.
Result: 4
● COLUMN()
It returns the column number of the inserted cell.
Result: 5
● COLUMN()-(COLUMN($E4)-1)
A subtraction operation will apply here.
Result: 1
● MID($B5,COLUMN()-(COLUMN($E4)- 1),1)
The MID function will operate here.
Result: 6
● Now, we will sort the separated numbers using the formula based on the SORT function.
=SORT($E$4:$H$4, ,1,TRUE)
You can also download the following Excel file for practice.
Solution Excel Sort Numbers.xlsx