How to Sort by Number in Excel (4 Techniques)

We often need to work with number lists where the numbers are randomly arranged. But arranging a number list from smaller to larger, or from larger to smaller numbers will help us to find the data way more easily. This article will show you how to sort by number in Excel in 4 easy ways.

Quick Solution

To sort by number in Excel,

  • Select the numbers first.
  • Go to the Data tab.
  • Click on the A to Z or Z to A icon to sort by number in Excel.

Click on the A to Z or Z to A icon to sort by number in Excel

If you are looking for keyboard shortcut keys to sort by number in Excel,

  • Press ALT ➤ A ➤ S ➤ A sequentially to sort in A to Z order.
  • Press ALT ➤ A ➤ S ➤ D sequentially to sort in Z to A order.

Technique #1: Use the A-Z or Z-A button to Sort by Numbers in Excel

You can also use the A-Z or Z-A buttons to sort in Excel by number. For that,

❶ Click on any single cell from the number column.

❷ Then click on the Data tab.

❸ Now from the Sort & Filter group, click on the A-Z button or Z-A button to sort numbers in an ascending or descending order, respectively.

Use the A-Z or Z-A button to Sort by Numbers in Excel

Final Result

I sorted the numbers in ascending order and the resulting data set is below:

Use A-Z and Z-A Commands from the Home tab

You can also find the A-Z and Z-A commands in the Home tab.

❶ Hit the Home tab.

❷ Under the Editing group, click on the Sort & Filter drop-down list

❸ Then click on the Sort A to Z/Sort Z to A command.

Use Sort A to Z/Sort Z to A command to sort by number in Excel

🔗 How to Sort Multiple Columns in Excel (4 Techniques)

Technique #2: Use the Filter Command to Sort by Number in Excel

To sort in Excel by number using the Filter command,

❶ Select any cell from the table.

❷ Click on the Data tab from the ribbon.

❸ Then select the Filter command under the Sort & Filter group.

Use the Filter Command to Sort by Number in Excel

Small arrows will pop up beside each of the headers of the data set.

❹ Click on the small arrow of the column containing numbers.

❺ Now from the drop-down, click on either the Sort Smallest to Largest option or the Sort Largest to Smallest option. Done!

click on either the Sort Smallest to Largest option or the Sort Largest to Smallest option to sort by number in Excel

Use Filter Command from the Home tab

You can also find the Filter command in another direction.

❶ Go to the Home tab.

❷ Then under the Editing group, click on the Sort & Filter drop-down list.

❸ Then select the Filter option.

Use Filter Command from the Context Menu

If a column contains names (not numbers), you can also filter the column alphabetically using the Filter command. To sort the names in Excel, click on the small arrow beside the header that contains names, in the drop-down Sort A to Z and Sort Z to A commands will show up.

🔗How to Sort in Excel by Name (3 Techniques)

Technique #3: Use the Custom Sort Command to Sort by Number in Excel

❶ Select the entire table.

❷ Then click on the Home tab.

❸ Then click on Sort & Filter drop-down list under the Editing group.

❹ Hit the Custom Sort command.

Use the Custom Sort Command to Sort by Number in Excel

Then a Sort dialog box will appear on Excel.

❺ From the Column section, click on the Sort by drop-down.

❻ Select your preferred column which contains numbers.

❼ Then click on the Order drop-down.

❽ Now select the Smallest to Largest command or Largest to Smallest command.

❾ Finally click OK.

Usage of the sort dialog box to sort by number in Excel

🔗 How to Sort in Excel by Date (3 Ways)

Technique #4: Use the Expanding Range to Sort by Number in Excel

1. Ascending Order

Syntax

SMALL(data_range,ROWS (expanding_range))

Here,

SMALL Function: Sets the lower numerical value in ascending position in a data set.

data_range: It is a range of cells that will be studied to find the smallest value.

ROWS Function: The total number of rows in a given array.

expanding_range: It is the expanding range that defines the difference from the smallest value to any cell.

❶ Double click on an empty cell where you want to place the ascending ordered list.

❷ Then write down the formula according to data_range & expanding_range in that cell.

For example, in my data set the numbers to start from the B2 cell and end with the B10 cell. So the formula was:

=SMALL($B$2:$B$10,ROWS($B$2:B2))

Usage of the SMALL function to sort by number in Excel


Formula Explanation

 Here, $B$2:$B$10 secures the range from B2 to B10. And the expanding_range shows the difference between the B2 cell to other cells by $B$2:B2.


❸ Press Enter after writing the formula.

❹ Then hold the cursor on the Fill Handle and drag it from cell C2 to C10. Done!

2. Descending Order

Sorting numbers in a downward order using a descending formula is as simple as the previous ascending formula. Just replace the SMALL function with the LARGE function.

Syntax

LARGE(data_range,ROWS (expanding_range))

 ❶ Double-click on an empty cell where you want to place the descending ordered list.

I have selected the D2 cell.

❷ Then write down the formula with the following data_range & expanding_range in that cell.

For instance, according to my data table, the formula was:

=LARGE($B$2:$B$10,ROWS($B$2:B2))

Usage of the LARGE function to sort by number in Excel

 ❸ Press Enter after writing the formula.

❹ Then hold the cursor on the Fill Handle and drag it till the end of the column.

🔗How to Sort Data in Excel Using a Formula (7 Formulas)

How to Sort by Number in rows in Excel

❶ Select the entire table.

❷ Go to the Home tab.

❸ Under the Editing group, click on the Sort & Filter drop-down.

❹ Select Custom Sort.

A Sort dialog box will pop up.

❺ Click on the Options command.

❻ Choose the orientation as Sort Left to Right.

❼ Press OK.

Sort by Number in rows in Excel

❽ Select your preferred row number from the Sort By drop-down list. I have selected row 10.

❾ Then change the Order from the drop-down. I have selected the Smallest to Largest option.

❿ Hit OK.

Here is the final result after sorting the rows from the smallest to largest order.

🔗 How to Sort in Excel by Color (3 Techniques)

Conclusion

So, I tried to cover as many approaches as possible to arrange numbers in ascending and descending orders in Excel. I hope all of the techniques will seem easy to apply. I am wishing this article will be useful for you. Feel free to leave a comment if you find anything difficult. Also, let us know which technique you have found the easiest.

(Visited 574 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *