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

While working with large Microsoft Excel, sometimes we need to transpose every n rows to columns. To transpose every n rows to columns is an easy task. This is a time-saving task also. Today, in this article, we’ll learn two quick and suitable ways to transpose every n rows to columns in Excel effectively with appropriate illustrations.

2 Suitable Ways to Transpose Every n Rows to Columns in Excel

Let’s assume we have an Excel large worksheet that contains the information about several sales representatives of Armani Group. The name of the sales representatives, their Identification Number, and the Revenue Earned by the sales representatives are given in Columns B, C, and D respectively. We can easily transpose every n row to columns using the INDEX Function. We also use a VBA code to transpose every n row to columns. For the convenience of our work, we will transpose every four rows into columns. Here’s an overview of the dataset for today’s task.

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

Using the INDEX function, we can easily transpose every n rows to columns in Excel. From our dataset, we will transpose every 4 rows into columns. To transpose every 4 rows to columns in excel is an easy task. This is a time-saving task also. Let’s follow the instructions below to transpose every four rows to columns!

Step 1:

• First of all, look at our dataset. We will transpose every 4 rows into columns.

• After that, select a cell. From our dataset, we will select cell F5 for the convenience of our work.

• After selecting cell F5, write down the below formula in the selected cell. The formula is,

• Hence, simply press ENTER on your keyboard and you will get David as the output of the INDEX function in cell F5.

Step 2:

• Further, autoFill the INDEX function to the rest of the cell of column F.

• Now, autoFill the INDEX function to the columns G, H, and I to get your desired output which has been given in the below screenshot.

Read More: How to Transpose Rows to Columns Based on Criteria in Excel (2 Ways)

Similar Readings

• How to Transpose Duplicate Rows to Columns in Excel (4 Ways)
• Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)
• How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
• Transpose Multiple Columns into One Column in Excel (3 Handy Methods)

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

Now I’ll show how to transpose every n rows to columns by using a simple VBA code. It’s very helpful for some particular moments. Applying VBA code to transpose every n rows to columns is an easy task. From our dataset, we will transpose every four rows into columns. Let’s follow the instructions below to transpose every four rows to columns!

Step 1:

• First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Transpose Rows to Columns will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

• Hence, the Transpose Rows to Columns module will appear in front of you. In the Transpose Rows to Columns module, write down the below 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

• Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

• After running the VBA Code, you will be able to transpose every four rows to columns which have been given in the below screenshot.

Read More: VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)

Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

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

File → Option → Customize Ribbon

Conclusion

I hope all of the suitable methods mentioned above to transpose every n rows to columns will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• How to Reverse Transpose in Excel (3 Simple Methods)
• How to Transpose Columns to Rows In Excel (6 Methods)
• Convert Columns to Rows in Excel Using Power Query
• How to Convert Columns to Rows in Excel Based On Cell Value
• Conditional Transpose in Excel (2 Examples)
• How to Convert Column to Comma Separated List With Single Quotes
• How to Transpose in Excel VBA (3 Methods)