How to Sort Rows in Excel (2 Simple Methods)

Sorting is one of the most common tasks in Excel, every now and then one may need to sort data. Most of our daily activities require column sorting, we hope that you are familiar with that, but if you need to perform row sorting? No worries, we are going to help you with this article.

Today we are going to show you how to sort rows in Excel. Before diving into the tutorial, let’s get to know about the workbook which is the base of our examples.

We have a dataset of several footballers along with their goals in the last five seasons in their respective leagues. We will sort rows using this data table.

Note that our dataset is a simple one, in the practical scenario you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Sort Rows in Excel

1. Sort using Excel tools

You can sort rows using the Excel featured tool. Let’s explore through examples.

I. Sort in Ascending Order

First of all, we will sort data in ascending order. Let’s start.

Select the row you want to sort and click Sort from the Sort & Filter section in the Data tab.

Since only one row has been selected from the table Excel will show you the Sort Warning dialog box.

Select Expand the selection and click Sort.

A Sort dialog box will pop up in front of you. Now click Options from the dialog box, you will find a new dialog box; Sort Options. 

Select the Sort left to right and click OK.

From the Sort by the drop-down box, you will get the name of the rows.

Select your preferred row and click OK. Here we have selected Row 4 (Ronaldo). And we have got the row sorted in ascending order.

Different columns shifted their positions to sort perfectly.

The name of the scorers also changed its position. If you are okay with this, then we can declare the success, but in most cases, you may need this not to happen.

So, let’s roll back to the Sort Warning dialog box and select the Continue with the current selection.

Once we explicitly select a particular row then we need not select the Sort left to right from the Options. Rather you will find the row is selected in the Sort dialog box.

Click OK. The row will be sorted in ascending order.

But this time, data is not organized correctly. To rectify the mistakes select the columns associated with the row we are going to sort.

Click the Sort operation. Now, select the sort left to right and click OK.

Select the desired row from the drop-down list of rows and click OK.

We will find row 4 (our selected one) sorted in ascending order.

To keep things easier for you, we have shown the result separately but remember when you use the sort feature in Excel you will find the result within the table itself.

II. Sort in Descending Order

The procedure will be similar to the previous one. Select the rows and columns as previous and click Sort.

Now, select the sort left to right from the Options in the Sort box and click OK. Since you are familiar with this we are not showing any image here.

Select your desired row at the Sort by list and select Largest and Smallest from the Order option and click OK.

This time we have selected Row 5 as our row. And Excel provided the sorted row (to keep things easier for you, we have shown the result separately).

2. Sort Rows Using Formulas

We can use the formula to sort the rows. We will use two different functions to create our formula for sorting rows in ascending and descending order respectively.

I. Sort in Ascending Order

To sort in ascending order we will use a function called SMALL. The SMALL function returns numeric values based on their position. To know more about this function, please visit the article; SMALL.

Now, to sort the data in ascending order, we need to write the formula in such a way that it will provide the number sequentially from the smallest value from the range to the largest value.

First, the smallest value, and for that our formula will be

We are using Row 4. Set 1, so that get the least value from this row.

Here 21 was the smallest value from the row and we found it through the formula.

Change the number to find the values in ascending order. Here we have used 1 to 5 since our row has 5 values.

The row has been sorted in ascending order. Here we have to set the heading and the athlete name manually.

II. Sort in Descending Order

To sort in descending order we will use a function called LARGE. Similar to the SMALL function the LARGE function returns numeric values based on their position. To know more about this function, please visit the article; LARGE.

Let’s find the highest value from the row (which is the initial stage), our formula will be the following one

This will provide the highest value from Row 4.

Now change the number to retrieve the second highest, third highest, and so on. Here we have used 1 to 5 since the row has 5 numbers in it.

You can see the row has been sorted in descending order. Again we have to set the heading and the athlete name manually.

Caution: This kind of sorting may cause you problems since you need to set the heading manually and when you have a bunch of data, some of them can be under the wrong column (s).  

Conclusion

That’s all for the session. We have listed a couple of ways to sort rows in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know which of the methods you are going to use. Notify us of the approaches which we might have missed here.