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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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.

transpose every n rows to columns in excel


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.

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

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

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

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

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

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

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

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


Similar Readings


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

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

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

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

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

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

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

 

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo