How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)

Download Practice Workbook

Transpose Rows to Columns.xlsm

Method 1 – Apply INDEX Function to Transpose Every n Rows to Columns in Excel

Step 1:

Select a cell. We will select cell F5.

Enter the following formula in the selected cell.

=INDEX($B:$B,ROW(B5)*4-4+COLUMN(B5))

Press ENTER and you will get David as the output of the INDEX function in cell F5.

Step 2:

AutoFill the INDEX function to the rest of the cell of column F.

AutoFill the INDEX function to the columns G, H and I to get the output as shown in the image below.

Similar Readings

Method 2 – Run a VBA Code to Transpose Every n Rows to Columns in Excel

Step 1:

Open a Module. From your Developer tab, go to,

Developer → Visual Basic

A window named Microsoft Visual Basic for Applications – Transpose Rows to Columns will appear. Insert a module for applying our VBA code. Go to,

Insert → Module

Step 2:

The Transpose Rows to Columns module will appear. Enter the following VBA,

Sub Transpose_Rows_to_Columns()
    Dim x As Long
    Dim y As Long
    Dim z As Long
    Application.ScreenUpdating = False
    z = Cells(1, Columns.Count).End(xlToLeft).Column
    For y = 1 To z Step 2
        x = x + 1
        Cells(1, y).Copy Destination:=Cells(x, 1)
        x = x + 1
        Cells(1, y + 1).Copy Destination:=Cells(x, 1)
    Next y
    Cells(1, 2).Resize(1, z - 1).Clear
    Application.ScreenUpdating = True
End Sub