How to Sort in Ascending Order in Excel (6 Suitable Methods) -

In this article, we will demonstrate some easy methods to sort data in ascending order in Excel. Sometimes it’s difficult to find data of interest from a random dataset. Sorting in ascending or descending order of the data can be helpful to search our desired values in an Excel sheet.

For example, let’s say, we have sales information about some products in the dataset. We want to know how the sales change for different products. If we sort the dataset based on sales in ascending order, we can find out the lowest sales on top and gradually increasing amount in the lower cells. This helps us to understand the change in sales amount whether it’s linear or exponential.

In the following image, you will have a general idea on how to sort in ascending order in Excel and how it affects the data.

Here, the sales data of different products are sorted using the Sort command.

A Brief Introduction to the Sort Feature in Excel

The Sort feature in Excel can operate differently based on commands. You have seen an overview of how the Sort feature works in the introduction. But here, we will scrutinize a little bit more on sorting data in ascending order in an Excel sheet.

Say, we have some numbers in a column randomly. After sorting in ascending order, it will look like the following picture.

But when the values have adjacent data or information, the sort command automatically rearranges those data.

The dataset on the left contains the sales of different products randomly while on the right, the dataset contains the sales amount sorted. But you can also see that the sorting here isn’t only applied to the Sales amount, it also rearranges the corresponding data around the sales amount like the size and product criteria. Later on, in this article, you will learn effective procedures to sort data in ascending order.

Read More: How to Add Sort Button in Excel

How to Sort in Ascending Order in Excel (6 Necessary Methods)

In the dataset, you have noticed that there is some sales information about different types of products in some particular dates. Let’s have a look at the data we will work on.

1. Applying Sort Feature to Sort Data in Ascending Order

We can easily sort any numbers or values using the Sort feature. There can be two possible sortings in a dataset.

1.1. Sort on Single Column

Please follow the video below to understand the procedure.

We showed the following steps in the above video.

Select any cell from the column that we want to sort.
Next, we selected Home >> Sort & Filter >> Sort Smallest to Largest.

After that, the entire table was sorted based on the Sales values.

Although it’s not a common practice to sort only the numbers and keep the other corresponding data unchanged, it may be required in some cases. In other words, we are sorting only a single column here. So, in the following video, I’ll show you how you can sort only a range of numbers.

The following tasks were done in the video.

First, select the data range (E5:E17).
Next, select Home >> Sort & Filter >> Sort Smallest to Largest.
A warning box will appear, showing what you want to do with this Sort option. Select ‘Continue with the current selection’ to sort the E5:E17 range only and click on the Sort button.

Thus, your data with selection will be sorted without affecting the other data.

1.2. Sort Multiple Columns (with Sort Priority)

Here, you will see the process of sorting multiple columns. We will sort by dates and sales amount.

To sort on multiple columns,

Select any cell of the Date.
Next, select Sort & Filter >> Custom Sort.

The Sort dialog box will appear. Select Date for the Sort by section from the drop down menus.
Make sure you set Sort On and Order options to Cell Values and Oldest to Newest.
After that, click on Add Level.

Similarly, add the parameters for the Sales column and click OK.

Now, your dataset is sorted by both dates and sales. Thus you can sort data by multiple columns.

Note: You can also find the Sort feature in the Data tab too.

Read More: How to Undo Sort in Excel

2. Sorting Dates from Context Menu in Ascending Order

We can also sort dates the way we sort numbers or currencies in ascending order. But in this section, we will use the sort command from the Context Menu. Please follow the video to understand this quick trick.

The following steps were covered in the video.

First, select any cell from the Dates
Next, right-click on that cell and select Sort >> Sort Oldest to Newest.

After that, you will see the dates sorted from oldest to recent which is actually an ascending order.

Read More: How to Sort Data by Value in Excel

3. Sort in Ascending Order Using Custom Sort Feature

In this section, we will learn an important feature of sorting in Excel. The basic way of sorting only allows us to sort based on the values of a single column or rows. But if we want to sort based on the order of custom priority, then we need to know about the Custom Sort feature. Please have a glance at the following instructions.

First, select any cell from the dataset.
Next, select Sort & Filter >> Custom Sort or press Alt + D + S to open the Custom Sort.

Here, we want to sort the sizes of the products first. Then each size will have the sales amount in ascending order. For that purpose,

Select the column heading Size in the Sort by box.
Set the Sort On option to Cell Values. However, in this case, it was set to Cell Values by default.
Select Custom List from the Order.

Now, in the Custom List dialog box, type the size categories in the List entries section and click on Add.
After that, click OK.

Now, watch the following video to understand the next procedures.

The below steps were taken in the video.

Select Add Level to add another Sort
Now, choose Sales in the Sort by
Next, click OK. Make sure the Sort On and Order sections are set to ‘Cell Values’ and ‘Smallest to Largest’ respectively.

Finally, you will see the Sales of products sorted in ascending order according to their sizes (Small, Medium, Large, and Extra Large).

4. Using SORT Function to Sort in Ascending Order in Excel

Another easy way to sort data in ascending order is to use the SORT function. Please go through the image below.

To sort the dataset based on Sales amount,

Select a cell where you want to keep the sorted dataset. Here, we selected the G5 cell in this case and typed the following formula.

=SORT(B5:E17,4,1,FALSE)

Here, B5:E17 is the base data range, the number ‘4’ indicates the column number of the selected data range which in this case is the Sales column and the values in this column will be sorted by rearranging the corresponding data. As we are sorting in ascending order, we set the [sort_order] argument to 1. We are sorting the data through rows, so we set False for the [by_col] argument.

All the methods above we described are not dynamic. However using formulas and an Excel table can make this process dynamic.

Let’s convert the dataset to a table. For this reason, you need to select the data range (B4:E17 for this case), press Ctrl + T and click OK. This step is not shown here.

Now, we want to insert a new entry in the table. Let’s say, the sales amount in the E18 cell will be between 376 and 50, so it should automatically sort between J9 and J10 cells in the range created by the SORT function. Here is the video to make further clarification.

In the video, you can see that there is a new entry in the table for large sized pants and the sales amount for pants was 450 bucks. This row can be found in the G10:J10 row of the range created by the SORT function because 450 is in between 376 and 465.50.

5. Sorting Filtered Data in Ascending Order

This section will cover how you can filter data and then sort it based on some values. Please follow the video below.

We did the following process to filter and sort the data.

First, select the column heading and press Ctrl + Shift + L. This will initiate the drop-downs of the Filter
Next, uncheck some products from the Product This will filter out those unwanted data.
Now, click on the drop-down icon beside the column heading ‘Sales’. Select Sort Smallest to Largest and this command will sort the filtered data in an ascending order.

Thus you can easily sort filtered data in ascending order in Excel.

Read More: How to Sort by Color in Excel

6. How to Sort Row in Ascending Order in Excel

All the methods we described above were about sorting through columns. But your numbered data can be in a single row in some cases too. Please continue to the following section to learn how you can sort in a single row in ascending order.

Say, we have a dataset containing overall sales data through months. We want to sort these sales in ascending order. To do that,

First, select any cell from the sales row (Row 5).
Press Alt + D + S to open the Sort dialog box.
In the Sort dialog box, click on Options to open the Sort Options dialog box.
After that, select Sort left to right and click OK.

Now, in the Sort dialog box, select Row 5 from the drop-down menu of the Sort by section as the sales data are in the 5th row.
Make sure you select Cell Values and Smallest to Largest from the Sort On and Order sections respectively.
Finally, click OK.

Thus, you can sort data in ascending order in a row.

How to Sort in Alphabetical Order in Excel?

Now we will see how to sort in alphabetical order instead of ascending order. Please watch the video below.

Here, we simply select a cell from a column that contains text. Then we just simply selected Sort & Filter >> Sort A to Z.

The command will sort the dataset according to the alphabetical order.

How to Sort Numbers Only Using Formula in Excel?

In this section, we will use a formula using SMALL and ROWS functions to sort numbers in another column.

Here, you need to copy the formula below and use the Fill Handle to AutoFill the lower cells with sorted data.

=SMALL($B$5:$B$17,ROWS($B$5:B5))

Here, we used this formula to sort data in ascending order. To sort data in descending order, we need to use another formula combining LARGE and ROWS functions.

Copy the formula below and drag the Fill Icon to AutoFill the lower cells with sorted data.

=LARGE($B$5:$B$17,ROWS($B$5:B5))

The advantage of using this procedure is that we can store the sorted data in another column and the main column remains the same. Also, we can use it dynamically with a table if needed.

How to Sort Rows to Put the Blank Cells on Bottom or Top in Excel?

If your data contains blanks, then simply using the Sort command will bring the blank cells to the bottom.

Select the data range and then go to Sort & Filter >> Sort Smallest to Largest.

After that, you will see the data sorted and the blank cells go to the bottom.

Now, we will see how we can sort data and keep the blank cells on top.

First, press Alt + F11 to open the VBA window.
Select Insert >> Module to open a VBA Module.

Insert the code below to the Module.

Here, we declared some variables and set a title for the InputBox. The InputBox will have the data range with blanks and the code will sort that range and keep the blanks on top. This operation is performed by various VBA properties like Range, OrderCustom, MatchCase etc.

In the video, we selected the data range and opened the Macro window by pressing Alt + F8.
After that, we run the Macro and an InputBox will appear.
Next, the data range with blanks is selected and we click OK.
This command will sort the data range keeping the blank cells on top.

Things to Remember

We have to keep in mind some potential issues while sorting data in ascending order.

Make sure no number data is stored as text. If that happens, convert the data in text form to any number
Also, clear any leading space in the dataset. This hampers the sorting To remove the leading spaces, use the TRIM function.

Download Practice Workbook

Download the following workbook to practice by yourself.

Sort Ascending Order.xlsm

Conclusion

In the end, we can conclude that you will learn some basic tips on how to sort in ascending order in Excel. We discussed sorting data in different ways based on the values in a column as it is the common practice. But we showed how to sort in ascending order in a row too as sometimes we may need this type of dataset.

If you have any questions, ideas, or feedback regarding this article, please leave them in the comment section. For more queries, visit our site ExcelDemy.

<< Go Back to Sort in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Sort in Ascending Order in Excel (6 Suitable Methods)