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.

Sorting by Number in Excel.xlsx

Table of Contents

Sort by Number in Excel Using the Sort Command

To sort by number in Excel with Sort command, follow these steps below:

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.

Shortcut: 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 (ascending).
Press ALT > A > S > D sequentially to sort in Z to A order (descending).

Sort by Numbers in Excel with the A-Z or Z-A button

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

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.

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.

Sort by Number in Excel Applying the Filter Command

Use Filter Command from Data tab

To sort in Excel by number using the Filter command, go through these steps below:

Select any cell from the table.
Click on the Data tab from the ribbon.

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

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

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

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

Done!

Use Filter Command from 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.

Sort by Number in Excel Using the Custom Sort Command

Use the Custom Sort command to sort by number in Excel. Now, go through the procedure below:

Select the entire table.
Then click on the Home tab.
Then click on the Sort & Filter drop-down list under the Editing group.

Hit the Custom Sort command.
Then a Sort dialog box will appear in 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.

Sort by Number in Excel with the Expanding Range

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.

Formula

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

Formula Explanation

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

To sort by number in Excel with the expanding range in ascending order, follow these steps:

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 start from the B2 cell and end with the B10 cell. So the formula was: =SMALL($B$2:$B$10,ROWS($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!

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

Formula

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

To sort by number in Excel with the expanding range in descending order, go through these steps:

Double-click on an empty cell where you want to place the descending order 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))

Press ENTER after writing the formula.
Then hold the cursor on the Fill Handle and drag it to the end of the column.

How to Sort by Number in rows in Excel

Sort by number in rows in Excel, here are the steps below:

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.
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 outcome after sorting the rows from the smallest to largest order.

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

Frequently Asked Questions

How do you sort in Excel by number and keep rows together?

To sort in Excel by number while keeping rows together, follow these steps:

Highlight the columns containing the numbers you want to sort, including any related data in other columns that you want to move together.
Press ALT + D + S sequentially to open the Sort dialog box.

In the Sort dialog box, select the column by which you want to sort under Sort by.
Choose Values from the drop-down menu to indicate that you are sorting by numerical values.
Choose the desired sorting order, either Smallest to Largest or Largest to Smallest.

If you have additional columns you want to sort by, click on Add Level in the dialog box and repeat steps 3-4 for each level.
Check the Preserve cell formatting box if you want to maintain the formatting of the cells while sorting.
Once you’ve configured your sorting options, click OK to apply the sort.

This will arrange the rows based on the numerical values in the specified column while keeping related data together. By following these steps, you can sort your data in Excel by number and ensure that the rows stay together, maintaining the relationships between different sets of data.

What is the shortcut for sort by number in Excel?

In Excel, the shortcut for sorting by number is:

Select the Range: Highlight the column of numbers you want to sort.

Apply the Shortcut: Press ALT + H + S + N sequentially.

This shortcut activates the Sort dialog box and automatically selects the Sort by Number option, allowing you to quickly sort the selected range numerically in ascending order.

Can you sort rows in numbers?

Yes, you can sort rows in Numbers. Follow these steps:

Open your Numbers spreadsheet.
Select the rows you want to sort.
Click on the Table menu.

Choose Sort Rows.
Specify the sorting options such as column and order (ascending or descending).

Numbers will then rearrange the selected rows based on your chosen criteria, allowing you to organize your data efficiently.

Rate this post