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

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

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

Insert → Module

Step 2:

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

• Run the VBA,

Run → Run Sub/UserForm

• You will be able to transpose every four rows to columns.

## Things to Remember

You can open Microsoft Visual Basic for Applications window by pressing Alt + F11.

If the Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF