To demonstrate putting numbers in order, we’ll use a dataset with three columns: “No.”, “Name”, and “Car”.
Method 1 – Using Context Menu to Put Numbers in Numerical Order in Excel
Steps:
- Select the cell range B5:B10.
- Right-Click to bring up the Context Menu.
- Select Sort then “Sort Smallest to Largest.”
A Sort Warning dialog box will appear.
- Select “Expand the selection.”
- Click on Sort.
This puts numbers in numerical order.
Method 2 – Put Numbers in Numerical Order in Excel by Utilizing Filter Menu
Steps:
- Select the cell range B4:D10.
- In the Home tab, go to Sort & Filter, and select Filter.
This will bring Filter buttons to our columns.
- Click on the Filter button of the “No.” column.
- Select “Sort Smallest to Largest”.
This sorts the numbers in ascending order.
Method 3 – Incorporating Sort Feature from Ribbon to Put Numbers in Numerical Order
Steps:
- Select the cell range B4:D10.
- In the Data tab, select Sort.
The Sort dialog box will appear.
- Select “No.” in the Sort by dropdown box.
- Check “My data has headers”.
- Press OK.
This arranges the first column in numerical order.
Method 4 – Put Numbers in Numerical Order in Excel by Applying SORT Function
Steps:
- Type the following formula in cell B13:
=SORT(B5:D10,1,1)Formula Breakdown
- We’re sorting the cell range B5:D10.
- There are two 1s 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 if we want to keep them as 1s.
- 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: Arrange Numbers in Ascending Order in Excel Using Formula
Method 5 – Combining SMALL & ROWS Functions to Place Numbers in Numerical Order
Let’s take a new dataset.
Steps:
- 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.
- Press Enter.
- Use the Fill Handle to AutoFill the formula.
This should put the numbers in numerical order.
Method 6 – Arranging Numbers in Numerical Order by Merging SMALL & ROW Functions
Steps:
- Select the cell range B5:D10.
- 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.
- Press Ctrl + Enter.
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 and newer.
- 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
<< Go Back to Sort Numbers | Sort in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
how to put my list in numerical order?
Hello Sameul Smith,
You can follow any of the methods of your choice to make your list. If you face any problem regarding it you can share your list with us. If you want to upload any image or Excel file you can post it on our ExcelDemy Forum.
Regards
ExcelDemy