Mehedi Hasan
Surprisingly, you may not find any inbuilt option to reverse a set of data in rows in Excel. It needs to be done manually. These are all the methods that you can use to reverse rows in Excel easily and save your time.
In this tutorial, we will show you how to reverse rows in Excel in 4 different ways. It includes Transposing Column method, Sort Feature technique, INDEX and COUNTA function combination and VBA method. We have used the below dataset as a sample.
1. Transposing Rows to Columns, Then Reversing Columns to Rows
Since sorting of rows is not available directly in Excel. In this method, we will transpose the rows to columns and then reverse the columns. Finally, the columns will be transposed to rows.
Steps:
- First, select the rows which you want to reverse and copy it by pressing CTRL+C.
- Secondly, paste it through a special paste option. Select Transpose from the Paste Special command.
- Now, your rows must turn into columns like below. Then add a Helper column containing numbers 1, 2, 3, sequentially.
- Now, select all the columns and navigate to Data. You will find the Sort command in the top middle of your Excel sheet.
- Click on Sort and a new window will appear like this.
- Then, select Helper from the Sort by drop-down list. Then click Largest to Smallest from the Order drop-down menu.
- The data stored in the columns must be reversed now.
- After that, delete the Helper Column.
- Finally, transpose the columns back to rows by using the Transpose from Paste Special options as mentioned in step 2.
2. Using Sort Feature to Reverse Rows in Excel
Rows can be reversed by adding a helper data row and utilizing the sort dialog box. This is a much easier method.
Steps:
- Add a Helper Row below the given rows containing numbers sequentially.
- Now, select the complete data set including Helper Row.
- Click on the Data tab and then click on the Sort.
- From the Sort dialog box, click on Options and select Sort Left to Right.
- After that, press OK and select the Helper Row (row 7) number from the Sort by drop down menu.
- Finally, select Largest to Smallest from the Order drop-down menu and click on OK.
- The rows are now reversed horizontally. Lastly, you may delete the Helper Row.
Similar Readings
- How to Reverse Text to Columns in Excel
- How to Reverse Data in Excel Cell
- How to Reverse Order of Columns Vertically in Excel
- How to Reverse Names in Excel
3. Combining INDEX and COUNTA Functions to Reverse Rows
A combination of INDEX and COUNTA functions can be used which is a bit tricky but a simple way to reverse rows. We have used a data set which contains 5 rows starting from row 4 and column C to G.
Steps:
- Select a cell outside the given data.
- Write down the following formula with the INDEX and COUNTA functions to reverse rows in Excel.
=INDEX($C$4:$G$4,1,COUNTA(C4:G4))
Formula Breakdown
- Here, the COUNTA function counts all the non-blank cells within the range C4:G4.
- The INDEX function returns a value of the cell at the intersection of a particular row and column in a given range which includes 3 parts. It takes input value as an array within the range C4:G4, 1 is the row number and the column number is the output of the previous COUNTA function.
- This will provide the reverse data.
- After that, select the cell and apply the AutoFill tool to the entire row in order to get the reverse data of the whole row.
- You can change the cell numbers in the formula and get the desired reversed rows.
Read More: How to Reverse Order of Data in Excel
4. Applying VBA to Reverse Rows in Excel
A VBA code can also help you to reverse data rows in Excel horizontally. A VBA code is necessary for this method.
Steps:
- Hold Alt+F11 keys in Excel which opens Microsoft Visual Basic Applications.
- Then click the Insert.
- Click on Module from the menu to create a module.
- A new window will open. Write the following VBA macro in the Module
Sub FlipRows()
‘ExcelDemy Publications
'Declaring Variables
Dim Gwf As Range
Dim WorkGwf As Range
Dim Cdd As Variant
Dim p As Integer, q As Integer, r As Integer
On Error Resume Next
xTitleId = "ExcelDemy"
Set WorkGwf = Application.Selection
Set WorkGwf = Application.InputBox("Range", xTitleId, WorkGwf.Address, Type:=8)
Cdd = WorkGwf.Formula
‘Applied For loop
For p = 1 To UBound(Cdd, 1)
r = UBound(Cdd, 2)
For q = 1 To UBound(Cdd, 2) / 2
xTemp = Cdd(p, q)
Cdd(p, q) = Cdd(p, r)
Cdd(p, r) = xTemp
r = r - 1
Next
Next
WorkGwf.Formula = Cdd
End Sub
- Press the F5 key to run the VBA code.
- A dialog box will open to select a range of rows.
- Select the desired Range you want to reverse and press OK.
- Finally, you will get all your rows reversed horizontally.
Read More: How to Use Excel VBA to Reverse String
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Conclusion
These were all the steps you can follow in Excel to reverse rows. Hopefully, you can now easily create the needed adjustment in rows. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
