In this article, we’re going to show you 6 methods of how to put numbers in numerical order in Excel. To demonstrate our methods, we’ve taken a dataset with 3 columns: “No.”, “Name”, and “Car”.
How to Put Numbers in Numerical Order in Excel (6 Easy Ways)
1. Using Context Menu to Put Numbers in Numerical Order in Excel
For the first method, we’re going to use the Context Menu to put numbers in numerical order.
Steps:
- Firstly, select the cell range B5:B10.
- Secondly, Right-Click to bring up the Context Menu.
- Thirdly, from Sort >>> select “Sort Smallest to Largest”.
A Sort Warning dialog box will appear.
- Then, select “Expand the selection”.
- Finally, click on Sort.
Thus, we’ll put numbers in numerical order.
Read More: How to Sort in Excel by Number of Characters
2. Put Numbers in Numerical Order in Excel by Utilizing Filter Menu
In this method, we’ll use the Filter menu to put the numbers in numerical order.
Steps:
- Firstly, select the cell range B4:D10.
- Secondly, from the Home tab >>> Sort & Filter >>> select Filter.
This will bring Filter buttons to our columns.
- Thirdly, click on the Filter button of the “No.” column.
- Finally, select “Sort Smallest to Largest”.
In conclusion, we’ve sorted our numbers in ascending order.
Read More: Arrange Numbers in Ascending Order in Excel Using Formula
3. Incorporating Sort Feature from Ribbon to Put Numbers in Numerical Order
For the third method, we’re going to use the Custom Sort feature from the Ribbon to put numbers in numerical order.
Steps:
- Firstly, select the cell range B4:D10.
- Secondly, from the Data tab >>> select Sort.
The Sort dialog box will appear.
- Thirdly, select “No.” in the Sort by dropdown box.
- Then, make sure to put a tick mark on “My data has headers”.
- Finally, press OK.
Consequently, we’ll arrange our first column in numerical order.
Read More: How to Sort Duplicates in Excel
4. Put Numbers in Numerical Order in Excel by Applying SORT Function
In this section, we’re going to use the SORT function to put numbers in numerical order.
Steps:
- Firstly, type the following formula in cell B13.
=SORT(B5:D10,1,1)Formula Breakdown
- Here, we’re sorting the cell range B5:D10.
- There are two 1 in this formula. The first 1 is to indicate our first column. Moreover, the second 1 is to get the sort in ascending order.
- Both of these values are default values. Hence, we can omit these and type the following formula also.
=SORT(B5:D10)
- Finally, press ENTER.
After that, this will AutoFill the formula to the rest of the cells. Moreover, the final step should look like this.
Read More: How to Sort by Last Name in Excel
5. Combining SMALL & ROWS Functions to Place Numbers in Numerical Order
In this method, we’ll use the SMALL and ROWS functions to put numbers in numerical order. Moreover, we’ve taken a new dataset.
Steps:
- Firstly, type the following formula in cell C5.
=SMALL($B$5:$B$10,ROWS($B$5:B5))Formula Breakdown
- ROWS($B$5:B5)
- Output: 1.
- The ROWS function returns the number of rows within a range. Our range is 1. Hence, the number of rows is 1.
- Our formula reduces to SMALL($B$5:$B$10,1)
- Output: 1.
- The SMALL function returns the kth smallest value from a range. Here, we’ll get the 1st smallest value from our B5:B10 range. Thus, we got 1.
- Secondly, press ENTER.
As we’ve explained above, we’ll get 1.
- Finally, use the Fill Handle to AutoFill the formula.
In conclusion, we’ve shown you yet another method to put numbers in numerical order.
6. Arranging Numbers in Numerical Order by Merging SMALL & ROW Functions
For the last method, we’ll use the ROW and SMALL functions to put numbers in ascending order.
Steps:
- Firstly, select the cell range B5:D10.
- Secondly, type the following formula.
=SMALL($B$5:$B$10,ROW(B5)-4)Formula Breakdown
- ROW(B5)-4
- Output: 1.
- The ROW function returns the row number of a cell. Here, ROW(B5) would return the value 5. However, we want the value 1, therefore, we’ve subtracted 4 from the cell.
- Our formula reduces to SMALL($B$5:$B$10,1)
- Output: 1.
- The SMALL function returns the kth smallest value from a range. Here, we’ll get the 1st smallest value from our B5:B10 range. Thus, we got 1.
- Thirdly, press CTRL + ENTER.
Thus, we’ve completed the last method of putting numbers in numerical order.
Read More: How to Sort Numbers with Letter Suffix in Excel
Things to Remember
- The SORT function is only available on Microsoft 365 and Office 2021.
- If there is some pre-existing value in the cell range B13:D18, we’ll get the “#SPILL” error.
- Make sure to use the absolute cell references in methods 5 and 6.
Practice Section
We’ve provided practice datasets for each method in the Excel file.
Download Practice Workbook
Conclusion
We’ve shown you 6 methods of how to put numbers in numerical order in Excel. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!
