8 Ways to Transpose Multiple Rows in Group to Columns in Excel

Microsoft Excel is an excellent tool for storing data in a more organized way. Usually, we store our data in different rows and columns. For that, sometimes we insert the categories in rows and their corresponding data in columns and vice versa. Hence, we face a situation where we need to swap the rows with columns, known as transposing. So, in this article, we will show 8 different ways to transpose multiple rows in a group to columns in excel.

📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.

Learn to Transpose Multiple Rows in Group to Columns in Excel with These 8 Methods

Firstly, let us get acquainted with our dataset used throughout the article as an example. This spreadsheet contains four columns- Name, Physics, Chemistry, and Math. In the first column, we have the student’s name, and in the following three columns, the number they got in Physics, Chemistry, and Math. Now we want to transpose the rows to columns, meaning we want the names in the first row and their numbers gotten in Physics, Chemistry, and Math in later Rows. Hence, we will use this dataset to transpose multiple rows in the group to columns in Excel.

First, we will use the Paste Special feature to transpose multiple rows in the group to columns. Pasting your copied cells in Excel automatically reformats copied text to match the destination content. However, you can paste it as a link or an image or use Paste Special to preserve the original formatting and transpose or swap the rows and columns. Now, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cells that you want to transpose. Here, we chose B5:E11.

• Then, Copy the selected cells. You can do it by going to the Home tab, selecting Copy from the Clipboard group, or pressing Ctrl+C.

• Next, select any cell with enough space to paste the transposed table; in our case, we need 7 columns and 4 rows. So, we selected cell B13.

• Then, go to the Clipboard group in Home Tab and select Paste Special.

• Then select the Transpose option from Paste Special Box.

• Or you can press Ctrl+V and select TRANSPOSE under the Paste option from the Context.

• Finally, the transposed table will look like this.

Now, in this second method, we will use the TRANSPOSE function option to transpose multiple rows in groups to columns. The TRANSPOSE function converts a vertical range of cells into a horizontal range or the other way around and needs to be entered as an array formula in a range with the same number of rows and columns as the source range. Let us see the steps to use the TRANSPOSE function.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cell where you want to transpose the dataset. We selected cell B13.

• Then enter the formula in the cell.

• Then, press Enter and the table will be transposed without formatting.

• Finally, you can customize the formatting of the table as you choose. We have formatted the table according to our format.

Next, we will use the INDIRECT function to transpose multiple rows in groups to columns. The INDIRECT function gives the reference that a text string has specified, and they are evaluated instantly to show their contents. When you wish to alter the reference to a cell within a formula without changing the formula itself, use INDIRECT. Now, let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

• We have to employ this function to consecutive cells. First, select Cell B13 and insert the function.

• Then select B14 and enter the function.

• After that, select B15 and enter the function.

• Next, enter the function in select B16.

• After that, select cells B13 to B16 and drag them to the H column to copy the function to other cells and the transposed table would look like this.

• Finally, after adding formatting, the transposed table would look like this.

📕 Read More: Transpose Formulas Without Changing References in Excel

Now, we will use INDIRECT & ADDRESS functions to transpose multiple rows in groups to columns. The ADDRESS function returns the address of a cell in a worksheet given row and column numbers. Other functions, such as the ROW and COLUMN, can give row and column number arguments for the ADDRESS function. For example, ADDRESS(5,2) returns $B$5, and ADDRESS(COLUMN(B5)) returns the column number, which is 2 in this case. This method combines the INDIRECT & ADDRESS functions to transpose the dataset. Let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• This method is similar to the previous method. First, we have to select Cell B13 and insert the formula,

• After that, select B14 and enter the function.

• Next, enter the function in select B15.

• Then, insert the formula in cell B16.

• After that select cells B13 to B16 and drag them to the H column to copy the function to other cells.

• After some formatting, we can get the following result.

This method will demonstrate how to transpose multiple rows in a group to columns using the INDEX function. The INDEX function returns a value or a reference to a value from a table or range of values. The INDEX function can be used in array form and reference form. In this method, we used the reference form, which returns the cell’s reference at the intersection of a specific row and column. You can choose which selection to look at if the reference comprises non-adjacent selections. Now, let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

• In this method, we will proceed similarly to the previous method. First, select Cell B13 and insert the formula,

• Insert the formula in cell B14

• Then in cell B15 Insert the formula.

• Next, insert the formula in cell B16.

• Lastly, select cells B13 to B16 and drag them to the H column to copy the function to other cells.

• Finally, after formatting, the transposed table will resemble the image below.

Power Query (also known as Get & Transform in Excel) allows you to import or connect to external data and then modify that data in ways that match your needs, such as removing a column, changing a data type, or merging tables. The query can then be loaded into Excel to generate charts and reports. We will use the Power Query tool from the ribbon to transpose multiple rows in a group to columns in excel. Let’s, see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cells which you want to transpose. We are selecting B5:E11.

• Then go to the Data tab in the ribbon and select From table.

• Then enter the range in Create Table.

• A new window named Power Query Editor will appear.

• Go to the Transform tab, select the Transpose option, and close the window.

• Then, in a new sheet, you will have your transposed table.

• The final result looks something like this.

Now, in this method, we will transpose a table and link it to Main Data. This method comes in handy when we want the data in the transposed table to change with the change in the main data. Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, copy the cells B5:E11 using Ctrl+C.

• Then, go to the Clipboard group in Home Tab and select Paste Special.

• And paste them in cell G5 using the Paste Link option from Paste Special.

• Next, select pasted cells and press Ctrl+H.
• Then Find and Replace named dialog box will appear and enter “=” characters in Find What and “xxx” in Replace with option and select Replace All.

• Then, the data set will look something like this.

• After that, copy this scary-looking range again by pressing Ctrl+C.
• Then, go to Paste Special from the clipboard group and select Transpose.

• Next, press Ctrl+H and do the opposite of the preceding step; that is, enter “xxx” characters in Find What and “=” in Replace With.

• After that, we will get our transposed rows and columns.

• After formatting the table will look like this.

We can transpose multiple rows in a group to columns easily by embedding Microsoft Visual Basic Application (VBA) in our workbook. Now, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

• At first, go to the Developer tab and select Visual Basic from the code group.

• Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.

• Enter the code and press F5.

Sub TransposeMultipleColumnsRows()

    Dim SRange As Range
    Dim DRange As Range
    Set SRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Multiple Rows to Columns", Type:=8)
    Set DRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Multiple Rows to Columns", Type:=8)

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

End Sub

• Now, to run the code press Alt+F8.
• Select Transpose Columns Rows and then Run.

• Next Select range B5:E11.

• And, select cell B13 as the destination range and press OK.

• Then, we will have transposed dataset in the selected range.

• Finally, after formatting, this is the transposed table.

📝 Takeaways from This Article

📌  This article demonstrated eight ways to transpose multiple rows in a group to columns in Excel.

📌  In the first method, we used Paste Special to transpose multiple rows in any group to columns in Excel.

📌  Then, we demonstrated TRANSPOSE, INDIRECT, a combination of INDIRECT & ADDRESS, and INDEX functions to transpose multiple rows in the group to columns in Excel.

📌  Next, we used the POWER QUERY tool to transpose multiple rows in the group to columns in Excel.

📌  Then we showed a way to link transposed table with the main data.

📌  Finally, we embedded VBA to transpose multiple rows in groups to columns.

Conclusion

This article has demonstrated 8 functions to transpose multiple rows in any group to columns in excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.

Related Articles

• Convert Column to Comma Separated List with Single Quotes