How to Reverse Rows in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Reverse Row in Excel 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.

Transposing column technique to reverse rows in Excel

  • Secondly, paste it through a special paste option. Select Transpose from the Paste Special command.

Paste special

  • Now, your rows must turn into columns like below. Then add a Helper column containing numbers 1, 2, 3, sequentially.

Using helper column to reverse excel rows

  • Now, select all the columns and navigate to Data. You will find the Sort command in the top middle of your Excel sheet.

Data and Sort option in Excel

  • 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.

Read More: How to Paste in Reverse Order in Excel


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.

reverse row in Excel through Sorting option

  • The rows are now reversed horizontally. Lastly, you may delete the Helper Row.


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))

INDEX and COUNTA function to reverse rows in Excel

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.

Reversing Row by using Functions

  • You can change the cell numbers in the formula and get the desired reversed rows.

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.

VBA Module to reverse rows in excel

  • 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

VBA Code to reverse Excel Rows

  • 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.

Select Range

  • 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.


Related Articles


<< Go Back to Excel Reverse Order | Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo