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

Get FREE Advanced Excel Exercises with Solutions!

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,
`=INDEX(\$B:\$B,ROW(B5)*4-4+COLUMN(B5))`

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

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

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF