How to Sort Rows in Excel (3 Easy Ways)

To demonstrate methods for sorting rows in Excel, we’ll utilize this simple dataset listing several footballers and their goals scored over the last five seasons.

how to sort rows in excel


 

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

We can sort rows using the Excel featured tool.

1.1 Sort Rows in Ascending Order

Step 1:

  • Select the row you want to sort. We will select the data range from C5 to C12.
  • From the Data tab, select

DataSort & Filter Sort

Sort Rows Data in Ascending Order

  • Since only one row has been selected from the table, Excel will present the Sort Warning dialog box. Select Expand the selection and click Sort.

  • The Sort dialog box will pop up. Click Options from the dialog box, which will open a new dialog box, Sort Options. From Sort Options, select Sort left to right and click OK.

Sort Rows Data in Ascending Order

  • In the Sort by dialog box, the names of the rows appear. Select your preferred row (we have selected Row 5), and click OK.

  • The result: the row is sorted in ascending order.

Sort Rows Data in Ascending Order

Step 2:

To prevent the index column (Scorer) from changing position:

  • Go back to the Sort Warning dialog box and select Continue with the current selection. Press Sort.

Sort Rows Data in Ascending Order

  • If we explicitly select a particular row, then we need not select Sort left to right from the Options. Rather, the row will be pre-selected in the Sort dialog box.

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

Sort Rows Data in Ascending Order

Step 3:

  • As result of Step 2, the data is not organized correctly. To rectify the mistakes, select the columns associated with the row we are going to sort.

Sort Rows Data in Ascending Order

  • Click Sort, then Options in the Sort dialog, then select Sort left to right and click OK.

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

Sort Rows Data in Ascending Order

  • Row 5 (our selected row) has been sorted in ascending order. For illustrative purposes, the sorted output is shown separately, but remember that when you use the sort feature in Excel, the result will appear within the table itself.

Read More: How to Sort Multiple Rows in Excel (2 Ways)


1.2 Sort Rows Data in Descending Order

The procedure is similar to Method 1.1.

Steps:

  • Select the rows and columns as before and click Sort.
  • Select the Sort left to right from the Options in the Sort box and click OK.

Sort Rows Data in Descending Order

  • Select the desired row from the Sort by list, then select Largest to Smallest from the Order drop-down list of options, and click OK.

  • This time we selected Row 6, which has been sorted in descending order. To illustrate, the result is again shown separately.

Sort Rows Data in Descending Order

Read More: How to Sort Rows by Column in Excel (4 Easy Methods)


Similar Readings


Method 2 – Sort Rows Using Formulas in Excel

We will use two different functions to create a formula for sorting rows in ascending and descending order respectively.

2.1 Applying the SMALL Function to Sort in Ascending Order

To sort in ascending order we will use a function called SMALL, which returns numeric values based on their position. For more information about this function, see the article SMALL.

To sort the data in ascending order, the formula must return the numbers sequentially from the smallest value in the range to the largest.

Steps:

  • Determine the smallest value, using the formula
=SMALL($C$9:$G$9,1)
  • We are using Row 9. Set parameter 3 to a value of 1, so that we get the lowest value in this row.
  • 12 is the smallest value in the row, and is correctly returned by the formula.

Applying SMALL Function to Sort in Ascending Order

  • Change the number in the 3rd parameter of the SMALL function to find the other values in ascending order. Here we use values 1 to 5 since our row has 5 values.

  • The row is sorted in ascending order. But we have to set the heading and the athlete’s name manually.

Applying SMALL Function to Sort in Ascending Order

Read More: How to Sort Data by Row Not Column in Excel (2 Easy Methods)


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 a numeric value based on position within a string. To find the highest value in the row (the first parameter of the function), use the following formula:

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

This formula returns the highest value from Row 12.

Using LARGE Function to Sort in Descending Order

  • Now change the number in the 3rd parameter of the LARGE function to retrieve the second highest, third highest, and so on. We use the values 1 to 5 since the row has 5 numbers in it.

  • The row is sorted in descending order. Again, we have to set the heading and the athlete name manually.

Using LARGE Function to Sort in Descending Order

Caution
This method of sorting can be problematic since the heading must be set manually and when you have a bunch of data, some of them can be returned in the wrong column(s). 

Read More: How to Sort Data in Excel Using Formula (2 Easy Methods)


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.

Steps:

  • Select the data range from B4 to G12
  • From the Data tab, select

DataSort & Filter Sort

Sort Rows Alphabetically and Keep Rows Together in Excel

  • The Sort dialog box opens. Select Scorer from the Sort by drop-down list, then Cell Values from the Sort On drop-down list, and A to Z from the Order drop-down list.
  •  Press OK.

  • The result: rows sorted are alphabetically by the Scorer column.

Sort Rows Alphabetically and Keep Rows Together in Excel

Read More: How To Sort Alphabetically In Excel And Keep Rows Together


Download Practice Workbook

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


Further Readings

Get FREE Advanced Excel Exercises with Solutions!
Shakil Ahmed
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo