How to Sort Rows in Excel (3 Easy Ways)

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, but if you need to perform row sorting? No worries, we are going to help you with this article. Today we’ll 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. Here’s an overview of the dataset for today’s task.

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 more complex dataset.

Download Practice Workbook

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

Sort Rows.xlsx

3 Suitable Ways to Sort Rows in Excel

Consider the following dataset. Let’s use this dataset to demonstrate what actions to take when sorting rows of data. We will use the Excel tools, the SMALL, and LARGE functions to sort rows of data in Excel.

Method 1: Using Excel Tools to Sort Rows Without Mixing Data

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

1.1 Sort Rows Data in Ascending Order

First of all, we will sort rows of data in ascending order. Let’s follow the instructions below to learn!

Step 1:

Select the row you want to sort. We will select the data range from C5 to Hence, from your Data tab, go to,

Data → Sort & Filter → Sort

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, and you will find a new dialog box; Sort Options. After that, select Sort left to right and click OK.

Hence, from the Sort by the drop-down box, you will get the name of the rows. Select your preferred row, we have selected Row 5, and click OK.

Here we have selected Row 5 (Ronaldo). And we have got the row sorted in ascending order.

Different columns shifted their positions to sort perfectly.

Step 2:

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 Continue with the current selection. Hence, press the Sort option.

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.

Step 3:

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

Click the Sort 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 5 (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.

1.2 Sort Rows Data in Descending Order

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

Steps:

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 images here.

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

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

Similar Readings

Method 2: Sort Rows Using Formulas in Excel

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

2.1 Applying SMALL Function to 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. Let’s follow the instructions below to learn!

Steps:

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

=SMALL($C$9:$G$9,1)

We are using Row 9. Set 1, so that we get the least value from this row.
Here, 12 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.

2.2 Using LARGE Function to 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. Let’s find the highest value from the row (which is the initial stage), our formula will be the following one

=LARGE($C$12:$G$12,1)

This will provide the highest value from Row 12.

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

Method 3: Sort Rows Alphabetically and Keep Rows Together in Excel

Last but not least, we will sort our dataset’s rows alphabetically using the Sort command. This is an easy and time-saving task as well. Let’s follow the instructions below to learn!

Steps:

Firstly, select the data range from B4 to After that, from your Data tab, go to,

Data → Sort & Filter → Sort

As a result, a Sort dialog box will appear in front of you. From the Sort dialog box, firstly, select Scorer from the Sort by drop-down list. Hence, select Cell Values from the Sort On drop-down list. Further, select A to Z from the Order drop-down list. At last, press the OK option.

Finally, you will be able to sort rows alphabetically using the Sort command which has been given in the below screenshot.

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.

How to Sort Rows in Excel (3 Easy Ways)

Download Practice Workbook

3 Suitable Ways to Sort Rows in Excel

Method 1: Using Excel Tools to Sort Rows Without Mixing Data

1.1 Sort Rows Data in Ascending Order

1.2 Sort Rows Data in Descending Order

Method 2: Sort Rows Using Formulas in Excel

2.1 Applying SMALL Function to Sort in Ascending Order

2.2 Using LARGE Function to Sort in Descending Order

Method 3: Sort Rows Alphabetically and Keep Rows Together in Excel

Conclusion

Further Readings

Leave a reply Cancel reply