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.

Dataset - How to Sort Rows in Excel

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.

Sort in Data tab - How to Sort Rows in Excel

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.

Select expand selection - How to Sort Rows in Excel

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. 

Sort left to right - How to Sort Rows in Excel

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 Row 4 - How to Sort Rows in Excel

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

Columns shift - How to Sort Rows in Excel

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.

Continue with current - How to Sort Rows in Excel

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.

Select Row 4 from one row selection - How to Sort Rows in Excel

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

Disordered sort - How to Sort Rows in Excel

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

Select entire data - How to Sort Rows in Excel

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

Sort left to right 2 - How to Sort Rows in Excel

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

Select Row 4 - 2 - How to Sort Rows in Excel

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

Sort result - How to Sort Rows in Excel

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.

Select entire data - How to Sort Rows in Excel

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.

Largest to smallest - How to Sort Rows in Excel

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

Sort result 2 - How to Sort Rows in Excel

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

=SMALL($C4:$G4,1)

SMALL - How to Sort Rows in Excel

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.

SMALL formula result - How to Sort Rows in Excel

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

SMALL formula 2 - How to Sort Rows in Excel

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

Sort ascending and set heading manually - How to Sort Rows in Excel

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

=LARGE($C4:$G4,1)

LARGE formula - How to Sort Rows in Excel

This will provide the highest value from Row 4.

LARGE result - How to Sort Rows in Excel

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.

LARGE formula 2 - How to Sort Rows in Excel

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

Sort result 2 - How to Sort Rows in Excel

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.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo