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.
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.
- First, select the dataset you want to sort.
- In the ribbon, go to the Home tab. Under the Editing group, select Sort & Filter. Then select Custom Sort from the drop-down menu.
- Now in the Sort box that popped up, in the Sort by field select Published, and in the Order field, select Smallest to Largest.
- Then click on OK. You will have your rows sorted.
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.
- First, select a range of cells equal to the dataset you are going to sort.
- Then write down the following formula.
- Now press Ctrl+Shift+Enter on your keyboard. You will have your dataset sorted by published year.
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.
- 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.
Note: This code will sort out all the row values of all columns.
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.
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.
- First, select the range of cells you want to sort.
- Then go to the Home tab and then select Editing> Sort & Filter > Custom Sort.
- 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.
- Finally, click on OK. You will have your rows sorted horizontally.
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.
- First, select a range of cells equal to the length of your dataset.
- Write down the following formula in the cell.
- Now press Ctrl+Shift+Enter on your keyboard. You will have your rows sorted horizontally.
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.
- First, select the range of cells.
- 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.
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.