How to Put Numbers in Numerical Order in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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


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.

How to Put Numbers in Numerical Order in Excel

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.

How to Put Numbers in Numerical Order in Excel

This will bring Filter buttons to our columns.

  • Thirdly, click on the Filter button of the “No.column.
  • Finally, select “Sort Smallest to Largest”.

How to Put Numbers in Numerical Order in Excel

In conclusion, we’ve sorted our numbers in ascending order.

How to Put Numbers in Numerical Order in Excel

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.

How to Put Numbers in Numerical Order in Excel

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)

How to Put Numbers in Numerical Order in Excel

  • 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.

How to Put Numbers in Numerical Order in Excel

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.

How to Put Numbers in Numerical Order in Excel

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.

How to Put Numbers in Numerical Order in Excel

  • 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#SPILLerror.
  • 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.

How to Put Numbers in Numerical Order in Excel


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!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo