Transpose Multiple Rows in Group to Columns with Formula in Excel

If you ever need to convert your Rows into Columns and Columns into Rows, this article will help you. Transposing your table in Excel can be messy. But fear not, as we will explain some easy ways you can save your time and transpose multiple Rows in group to Columns using formula in Excel.

📁  Download Excel File

Learn to Transpose Multiple Rows in Group to Columns Using Formula with These 5 Methods in Excel

In order to transpose a dataset, the rows and columns must be switched so that the rows become the columns and the columns are the rows. Explicitly, we will use a dataset of 6 Rows and 4 Columns which will rotate to 4 Rows and 6 Columns. In this article, we not only explained some built-in features offered by Excel but also showed how you can use some functions like TRANSPOSE, INDIRECT, ADDRESS, COLUMN, and ROW to formulate formula to transpose multiple Rows in group to Columns. Again, we tried to create VBA code to avert some of the limitations offered by the functions. Let’s dive into the article.

For our first method, we will see how Excel’s features work to transpose multiple Rows in groups to columns. Let’s learn about them in detail.

The easiest way we can transpose multiple Rows in groups into Columns in Excel is by using keyboard shortcuts.

⬇️⬇️ STEPS ⬇️⬇️

• First, choose the range you wish to transpose first. For our case, we selected, B5:E10.
• Then, press Ctrl+C which will copy the range.
• Next, select the first cell of your desired output range. We choose B13.
• Now, press Ctrl+V.

• Then choose Transpose from the drop-down menu.

Consequently, you’ll obtain the desired outcome.

📕 Read More: Convert Column to Comma Separated List with Single Quotes

We can also apply the Paste Special option like this.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the entire data range. Then, press Ctrl+C to copy the cells you want.

• Then, choose the very first cell in the target range. We selected B13. Right-click on the selected cell and the Context Menu will appear. Here, choose Paste Special.

• Finally, select Transpose in the Paste Special dialogue box and Press OK.

As you’ve just seen, the Paste Special function enables you to change data from row to column (or column to row) in a matter of seconds. Additionally, this approach duplicates the layout of your original data, which strengthens its case even further.

On the other hand, Ribbon can offer some assistance in this case.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the range that has to be transposed and then, copy it using Ctrl+C.

• Then select the first cell of your output range and go to the Home tab and in the left corner of the Ribbon, select A drop-down menu will appear.

• Finally, press Transpose to see the results.

The TRANSPOSE function, as its name implies, is specifically developed for transposing data in Excel. Let’s learn about the function.

Syntax of the function:

An array is transposed by making the first row of the array the first column of the new array, the second row of the array the second column of the new array, and so on.  Let’s apply the function to our table.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the first cell of the area you want to transpose. We choose B13.
• Then, write the following formula in the Formula Bar.

• Finally, Press Enter to apply the formula.

As shown in the screenshot above, the original table formatting is not retained in the converted table. This is one of the problems with the TRANSPOSE function.  So you have to format it again for the transposed one. After formatting, this is what our table looks like.

Another function we can use to create a formula to transpose multiple Rows in group to Columns in Excel that is useful is the INDIRECT function. The INDIRECT function in Excel returns a legitimate cell reference from a text string. When you wish to construct a text value that may be used as a legitimate reference, INDIRECT is beneficial. However, we can also use this function to transpose.

Syntax of the INDIRECT function:

Using the INDIRECT function alone won’t get our job done.  We need to call another function for this purpose.  We will also use the COLUMN function.

Syntax of COLUMN function:

The COLUMN function gives a reference’s column number. COLUMN(B5), for example, gives 2 because B is the 2nd column in the spreadsheet.  If you don’t provide any references, the function will return the selected cell’s index number.  Let’s just transpose our data range using the INDIRECT and COLUMN functions.

⬇️⬇️ STEPS ⬇️⬇️

• As usual, First, select the first cell where you want to put your transpose table. We choose B13 and type the following formula and hit Enter.

• Next, go to the next row B14 and type this formula, and hit Enter.

• Keep this up for every column you have in the original table. So, in cell B15, we type,

And for cell B16,

• Finally, select the 4 cells together and drag them to your right side. Since we have 6 Rows in the original data, we will need to drag the selected cells for another 5 columns.

See the magic as your Rows turn to Columns and Columns turn to Rows. You also have to edit the formatting of the table in this case.

📕 Read More: Transpose Formulas Without Changing References in Excel

We can also nest the ADDRESS function into the INDIRECT function to create a formula to transpose multiple Rows in group to Columns in Excel. Additionally, we will use the COLUMN and ROW functions as well.

Syntax of ADDRESS function:

Syntax of ROW functions:

The Row function is the same as the COLUMN function. It only gives the row index number.

⬇️⬇️ STEPS ⬇️⬇️

• First, select B13 as this is where our transposed table will start, and type the following formula.

• Press Enter and drag the Fill Handle tool downward. Your table headers will turn into rows.

• Finally, select the rows and drag the Fill Handle tool to your right to get the remaining data converted.

As you can see, you will need to format the table again to make it look better.

By transforming our data range into a table, we can also transpose. Let’s see how.

⬇️⬇️ STEPS ⬇️⬇️

• First, select your entire data range as we selected B5:E10.
• Next, go to Insert and select Table.

• Consequently, a dialogue box will show up. You will see the range we selected is housed here. Further, check the box behind My Table has Headers. Then click OK.

• As a result, your data range has turned into a Table. Next, go to Data Tab and select From Table/Range.

• Thus, a Power Query Editor will show up. Here, go to Transform.

• Then, select Use First Row as Headers and then Use Headers as First Row.

You will notice another Row has appeared before the first Row of your Table.

• Finally, select Transpose to transpose the table.

Henceforth, your Table has transposed successfully.

If you like, we can use a VBA code to accomplish the same thing. With this in mind, you must first open the Developer tab in order to begin writing your code. To learn how to get to the Developer tab, click here.  Let’s see how we can transpose multiple rows to columns in groups using Excel’s coolest version.

⬇️⬇️ STEPS ⬇️⬇️

• First, select Visual Basic from the Developer tab.

• Next, we will enter our code in the Microsoft Visual Basic for Applications window that appears after choosing Module from the Insert.

• In the white module, copy and paste the formula below.

Sub Transpose()
  Dim SourceTable As Range
  Dim OutputTable As Range

  Set SourceTable = Application.InputBox(Prompt:="Please Select the Table to Transpose", Title:="Transpose Rows to Columns", Type:=8)
  Set OutputTable = Application.InputBox(Prompt:="Select the First cell of the Output Table", Title:="Transpose Rows to Columns", Type:=8)

  SourceTable.Copy
  OutputTable.Select
  Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
  Application.CutCopyMode = False

End Sub

• Next, press F5 to execute the code.
• At this moment. a box will show up demanding the table you want to transpose. Select your table as we selected B5:E10 and press OK.

• Finally, another pop-up box will appear to know the first cell of your output transpose table, provide that and press OK.

Your table will be transposed as shown below.

📄 Important Notes

🖊️  Utilizing the TRANSPOSE function, the transposed table preserves the relationship to the source table, and when the source data changes, it does the transposed table.

🖊️  If any cells in the original table are empty, the rotated cells will display 0 instead.

🖊️  VBA code will retain the original formatting of the Table.

📝 Takeaways from This Article

📌  In the first method, we showed how users can use the built-in features of Excel in a prolific way.

📌  The second method showed the use of the TRANSPOSE function.

📌  The third Method describes how users will use the INDIRECT function with the assistance of the COLUMN and ADDRESS functions.

📌  Users can also transpose data from the property of Table.

📌  Finally, a VBA code was provided to ease the process.

Conclusion

Finally, we have concluded this article.  I hope I articulated this article in simple language. If you have any questions, feel free to comment below.  Moreover, if you know of any easier methods, please share them below. For more tutorials, visit Excelden.com.