How to Reverse Rows in Excel (4 Easy Ways)

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 which you can use to reverse rows in Excel easily and save your time.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


4 Easy Ways to Reverse Rows in Excel

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 Columns

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 Reverse Transpose in Excel (3 Simple Methods)


2. Utilizing 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

Read More: How to Reverse Text to Columns in Excel (6 Handy Methods)


Similar Readings


3. Combining INDEX and COUNTA Functions to Reverse Rows in Excel

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.

Read More: Reverse Order of Data in Excel (4 Useful Methods)


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 Open Workbook from Path Using Excel VBA (4 Examples)


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.
For more information like this, visit Exceldemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo