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

To demonstrate putting numbers in order, we’ll use a dataset with three columns: “No.”, “Name”, and “Car”.

How to Put Numbers in Numerical Order in Excel


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.

How to Put Numbers in Numerical Order in Excel

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.

How to Put Numbers in Numerical Order in Excel

This will bring Filter buttons to our columns.

  • Click on the Filter button of the “No.column.
  • Select “Sort Smallest to Largest”.

How to Put Numbers in Numerical Order in Excel

This sorts the numbers in ascending order.

How to Put Numbers in Numerical Order in Excel


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.

How to Put Numbers in Numerical Order in Excel

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.

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

How to Put Numbers in Numerical Order in Excel

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.

How to Put Numbers in Numerical Order in Excel

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.

How to Put Numbers in Numerical Order in Excel

  • Press Ctrl + Enter.


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#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


<< Go Back to Sort Numbers | Sort in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

2 Comments
  1. 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo