Rafiul Haq
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”.
Download Practice Workbook
6 Ways to Put Numbers in Numerical Order in Excel
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: Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)
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: [Fix:] Sort and Filter Not Working in Excel
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 Numbers in Excel (8 Quick Ways)
Similar Readings
- How to Use Excel Shortcut to Sort Data (7 Easy Ways)
- How to Sort Unique List in Excel (10 Useful Methods)
- [Solved!] Excel Sort Not Working (2 Solutions)
- How to Add Sort Button in Excel (7 Methods)
- How to Sort IP Address in Excel (6 Methods)
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 Use Sort Function in Excel VBA (8 Suitable Examples)
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.
Read More: How to Sort Data in Excel Using Formula
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 Arrange Numbers in Ascending Order in Excel Using Formula
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.
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!
Related Articles
- How to Sort Multiple Columns in Excel (5 Quick Approaches)
- How to Sort Columns in Excel without Mixing Data (3 Ways)
- Excel Sort Dates in Chronological Order (6 Effective Ways)
- Sum Using OFFSET and MATCH in Excel (With Alternative Options)
- Excel Sort By Date And Time [4 Smart Ways]
- How to Sort Multiple Columns in Excel Independently of Each Other
