In this article, we will learn to sort data by row not column in Excel. By default, Excel sorts data by column. But in some cases, we need to sort data by row in our dataset. Today, we will discuss 2 methods to sort data by row not column in Excel.
Download Practice Book
Download the practice book here.
2 Methods to Sort Data by Row Not Column in Excel
To explain these methods, we will use a dataset that contains information about the Sales Amount for the first four months of some sellers.
1. Use Excel Custom Sort to Sort Data by Row Not Column
We can use the custom sort option in excel to sort data by row. In excel, we can sort data by a single row and also by multiple rows. In the following sub-methods, we will discuss both of them.
1.1 Sort Single Row
In this sub-method, we will try to sort data by a single row. Here, our dataset will contain two rows with values and a header.
Let’s follow the steps below to learn more.
- First of all, select the range in your dataset. We have selected Cell B5 to F7.
- Secondly, go to the Home tab and select Sort & Filter. A drop-down menu will occur.
- Select Custom Sort from the drop-down menu. It will open the Sort window.
- Thirdly, check ‘My data has headers’ if your dataset has headers.
- Then, select Options.
- After selecting options, a dialog box will pop up. Select Sort left to right and click OK.
- After that, select the row number from the Row field and the order you want to sort the data from the Order field.
- We want to sort the dataset by Row 6 in descending order. So, we have selected Row 6 in the Row field and Largest to smallest in the Order field.
- Finally, click OK to see results like the image below. Here, you can see that Row 6 is sorted in descending order. And the dataset is sorted by row.
1.2 Sort Multiple Rows
You can also sort data by multiple rows. This procedure is almost the same as the previous sub-method. Here, our dataset will contain multiple rows with values. So, without further ado, let’s observe the steps below.
- In the first place, select the range where you want to apply the Custom Sort.
- Secondly, go to the Home tab and select Sort & Filter.
- Then, select Custom Sort from the drop-down menu.
- Now, check ‘My table has headers’ if your selected dataset contains headers.
- After that, select Options. A message box will appear.
- Select Sort left to right from the message box and click OK to proceed.
- Select the row by which you want to sort the data first and then, select the order.
- Next, select Add Level to add another row.
- Here, select the row by which you want to sort the data second, and then, select the order.
- Do the same if you want to add more rows.
- Finally, click OK to see results like the picture below. Here, the dataset is sorted by Row 6, and then by Row 7, Row 8, and Row 9 respectively.
Read More: How to Sort Multiple Rows in Excel (2 Ways)
- How to Put Numbers in Numerical Order in Excel (6 Methods)
- Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)
- How to Auto Sort in Excel without Macros (3 Methods)
- Excel Sort by Column without Header (5 Methods)
- [Fix:] Sort and Filter Not Working in Excel
2. Sort Data by Row Not Column with Excel VBA
VBA is always useful to perform different tasks in excel very easily. Today, we will show some short macro to sort data by row in our excel worksheet. Here, we will use a dataset that describes the sales amount of some sellers.
Let’s pay attention to the steps below to learn more.
- To begin with, go to the Developer tab and select Visual Basic. It will open the Visual Basic window. You can also open it by pressing Alt + F11.
- In the Visual Basic window, select Insert and then, Module. This will open the Module window. Or, double-click on the sheet where you want to apply the code to open the Module window.
- Now, type the code in the Module window to sort by row in ascending order:
Sub Sort_by_Row() Range("C5:F8").Sort Key1:=Range("C5"), _ Order1:=xlAscending, _ Orientation:=xlSortRows End Sub
- After that, press Ctrl + S to save the code and then, close the Visual Basic window.
- Next, select Macros from the Developer tab to open the Macro window.
- Then, select and Run the code from the Macro window.
- After running the code, you will see results like the screenshot below.
- To sort by row in descending order, type the code below in the Module window:
Sub Sort_by_Row() Range("C5:F8").Sort Key1:=Range("C5"), _ Order1:=xlDescending, _ Orientation:=xlSortRows End Sub
- In the end, run the code by pressing the F5 key to see results in descending order.
Read More: VBA to Sort Column in Excel (4 Methods)
Things to Remember
In both methods, we have sorted the dataset by the first row of our selected range. It is not possible to sort each row individually in ascending or descending order.
We have demonstrated 2 easy methods to Sort Data by Row not Column in Excel. We have also discussed the method with VBA. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
- How to Use Excel Shortcut to Sort Data (7 Easy Ways)
- How to Sort Array with Excel VBA (Both Ascending and Descending Order)
- [Solved!] Excel Sort Not Working (2 Solutions)
- How to Sort IP Address in Excel (6 Methods)
- Sort by Month in Excel (4 Methods)
- How to Sort Birthdays by Month and Day in Excel (5 Ways)