How to Sort Multiple Rows in Excel (2 Ways)

Sorting rows is one important task in any form of data analysis. Excel provides different methods to sort rows in different scenarios with different results. In this tutorial, I am going to show you how to sort multiple rows in Excel.


Download Practice Workbook

You can download the workbook with all the examples used to demonstrate this guide below.


2 Ways to Sort Multiple Rows in Excel

I will be covering two major different rows sorting in Excel here, Vertical and Horizontal. The first one focuses on vertical sorting and the second one is horizontal row sorting. Each has its own sub-methods to perform the operations.


1. Sort Multiple Rows Vertically in Excel

There are three major ways you can sort out a big chunk of unorganized data in Excel. I am going to show each of them in their individual sections. But, for each one, I will be using the following dataset.

It is a list of best-selling books of all time. I will be sorting this dataset out into different categories in each section.

Read More: How to Sort Rows by Date in Excel (8 Methods)


1.1 Using Custom Sort Command

The Sort & Filter tool is one of the most used tools in all of Excel and can be used to easily sort multiple rows or columns in Excel. In this tool, there is a Custom Sort command that I will be using in the dataset to sort out which books came out first by sorting them by their published year.

Steps:

  • First, select the dataset you want to sort.

How to Sort Multiple Rows in Excel

  • In the ribbon, go to the Home tab. Under the Editing group, select Sort & Filter. Then select Custom Sort from the drop-down menu.

How to Sort Multiple Rows in Excel

  • Now in the Sort box that popped up, in the Sort by field select Published, and in the Order field, select Smallest to Largest.

How to Sort Multiple Rows in Excel

  • Then click on OK. You will have your rows sorted.

How to Sort Multiple Rows in Excel

Read More: How to Create Custom Sort in Excel (Both Creating and Using)


1.2 Applying SORT Function

There is a built-in function called the SORT function to sort multiple rows or columns in Excel. The function takes one primary and multiple optional arguments. It takes an array as the primary argument that it is going to sort. The secondary arguments include sort index, sort order(1 for ascending and -1 for descending order), and a boolean depending on if you are sorting column or row.

I am going to use the same dataset to achieve the same result. But, this time, with the SORT function.

Steps:

  • First, select a range of cells equal to the dataset you are going to sort.
  • Then write down the following formula.

=SORT(B5:E14,3,1)

  • Now press Ctrl+Shift+Enter on your keyboard. You will have your dataset sorted by published year.

How to Sort Multiple Rows in Excel

Read More: How to Use Sort Function in Excel VBA (8 Suitable Examples)


1.3 Embedding VBA Code

You can use Microsoft Visual Basic for Applications (VBA) to sort out multiple rows in Excel. To do this, you first need the Developer tab shown on your ribbon. If you don’t have that, enable the Developer tab.

Once you have the tab, follow these steps to get sorted rows. I will be using the same dataset as the above methods for this one.

Steps:

  • First, in the Developer tab, under the Code group, select Visual Basic.

  • After that, a VBA window will pop up. In it, go to Insert>Module.

  • Now, in the module, copy the following code.
Sub Sort_Individual_Rows()
    Dim xRnge As Range
    Dim yRnge As Range
    Dim wsht As Worksheet
    Set wsht = ActiveSheet
    On Error Resume Next
    Set xRnge = Application.InputBox(Prompt:="Range Selection:", _
                                    Title:="Powered by Exceldemy.com", Type:=8)
    Application.ScreenUpdating = False
    For Each yRnge In xRnge
        With wsht.Sort
            .SortFields.Clear
            .SortFields.Add Key:=yRnge, Order:=xlAscending
            .SetRange wsht.Range(yRnge, yRnge.End(xlDown))
            .Header = xlNo
            .MatchCase = False
            .Apply
        End With
    Next yRnge
    Application.ScreenUpdating = True
End Sub
  • Once you are done, press F5 on your keyboard to run the code.
  • Now a range selection box will pop up. Select the range of cells B5:E14.
  • Then click on OK and you will have your row values sorted.

How to Sort Multiple Rows in Excel

Note: This code will sort out all the row values of all columns.

Read More: VBA to Sort Column in Excel (4 Methods)


Similar Readings


2. Sort Multiple Rows Horizontally in Excel

You can sort multiple rows horizontally in Excel. Let’s say you have a dataset horizontally aligned like this.

You may want to sort the rows in ascending or descending manner now. You can achieve the sorted result for these rows by the three methods again.

Read More: How to Sort Multiple Columns with Excel VBA (3 Methods)


2.1 Applying Custom Sort Command

First, I will be using the Excel Custom Sort command to sort these rows horizontally. For a detailed guide follow these steps.

Steps:

  • First, select the range of cells you want to sort.

How to Sort Multiple Rows in Excel

  • Then go to the Home tab and then select Editing> Sort & Filter > Custom Sort.

How to sort multiple rows in Excel

  • Now, in the Sort box, select Row 5 or Row 6 depending on which row you want to sort in the Sort by field, and select Smallest to Largest in the Order field.

How to Sort Multiple Rows in Excel

  • Finally, click on OK. You will have your rows sorted horizontally.

Read More: How to Create Custom Sort List in Excel


2.2 Utilizing SORT Function

You can also use the SORT function to sort multiple rows in Excel horizontally. The function can take several arguments. It takes an array as the primary argument. The secondary arguments include sort index, sort order(1 for ascending and -1 for descending order), and a boolean depending on if you are sorting column or row.

For a more detailed guide, follow these steps.

Steps:

  • First, select a range of cells equal to the length of your dataset.

How to Sort Multiple Rows in Excel

  • Write down the following formula in the cell.

=SORT(C5:H6,1,1,TRUE)

How to Sort Multiple Rows in Excel

  • Now press Ctrl+Shift+Enter on your keyboard. You will have your rows sorted horizontally.

How to Sort Multiple Rows in Excel

Read More: How to Use Sort Function in Excel VBA (8 Suitable Examples)


2.3 Embedding VBA Code

To achieve the result by using Microsoft Visual Basic for Applications (VBA) you need the Developer tab showing on your ribbon. If you don’t have that, enable the Developer tab.

Once you have the tab, follow these steps to sort multiple rows in Excel horizontally.

Steps:

  • First, select the range of cells.

How to Sort Multiple Rows in Excel

  • After that, in your ribbon, go to the Developer tab. And in the Code group, select Visual Basic.

  • Now in the VBA window that popped up, go to Insert and select Module.

  • Then in the Module, copy the following code.
Sub Sort_Rows()
    Dim xRnge As Range
    Dim yRnge As Range
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set xRnge = Selection

    If xRnge.Count = 1 Then
        MsgBox "Please select multiple cells!", vbExclamation, "Powered by Exceldemy.com"
        Exit Sub
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With

    Application.ScreenUpdating = False
    For Each yRnge In xRnge.Rows
        yRnge.Sort Key1:=yRnge.Cells(1, 1), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        Orientation:=xlSortRows
    Next yRnge

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With

    Application.ScreenUpdating = True
End Sub
  • Finally, press F5 to run the code. It will sort your rows horizontally.

How to Sort Multiple Rows in Excel

Read More: How to Use Excel Shortcut to Sort Data (7 Easy Ways)


Conclusion

These were the different methods you can use to sort multiple rows in Excel both vertically and horizontally. Hope you have found this article helpful and informative. If you have any questions or suggestions let us know down below. For more guides like this visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo