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.

**Method 1**

**1. Using**** Paste Special Feature**

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.

**Method 2**

**2. Applying TRANSPOSE Function**

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.

`=TRANSPOSE(B5:E11)`

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

**Method 3**

**3. Employing INDIRECT Function**

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.

`=INDIRECT("B"&COLUMN()+3)`

**🔨 Formula Breakdown**

👉 Here, we used the **INDIRECT** function, which returns the reference that a text string has specified. We want to refer to cell **B5** here. So, we entered this formula, **=INDIRECT(“b”&COLUMN()-2)**

where **“B”** refers to **B** column. Additionally, the **COLUMN** function returns the value of the column number of the cell in which the formula has been inserted. Here we inserted the formula in **B13** column so it would return 2, but we wanted the value to be 5, so we entered **“+3”**. Thus, we get cell reference **B5 **inside the **INDIRECT** function and get the text of cell** B5** as output in** B13**.

- Then select
**B14**and enter the function.

`=INDIRECT("C"&COLUMN()+3)`

- After that, select
**B15**and enter the function.

`=INDIRECT("D"&COLUMN()+3)`

- Next, enter the function in select
**B16**.

`=INDIRECT("E"&COLUMN()+3)`

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

**Method 4**

**4. Applying INDIRECT & ADDRESS Functions**

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,

**=INDIRECT(ADDRESS(COLUMN(B5) – COLUMN($B$5) + ROW($B$5), ROW(B5) – ROW($B$5) + COLUMN($B$5)))**

**🔨 Formula Breakdown**

Here, the formula in cell **B13** is

**INDIRECT(ADDRESS(COLUMN(B5) – COLUMN($B$5) + ROW($B$5), ROW(B5) – ROW($B$5) + COLUMN($B$5)))**

👉 The **COLUMN** & **ROW** function consecutively returns the cell reference’s column and row numbers. Like, here **COLUMN(B5)** and ** COLUMN($B$5)** both gives the value 2 and **ROW($B$5)** gives 5.

👉 Similarly, from **ROW(B5) – ROW($B$5) + COLUMN($B$5)** in this part, we get the value 2.

👉 So, we have inside** ADDRESS** function “5,2”, which refers to cell** B5**

and** INDIRECT** function then displays the value in cell **B5**.

👉 Similarly, the formula in cell** B14** is **INDIRECT(ADDRESS(COLUMN(B6) – COLUMN($B$5) + ROW($B$5), ROW(B6) – ROW($B$5) + COLUMN($B$5)))**

👉 Here,** COLUMN(B6) – COLUMN($B$5) + ROW($B$5)** gives the value 5 and **ROW(B6) – ROW($B$5) + COLUMN($B$5)** gives 3. So, in the **ADDRESS** function, we have “5,3”, which refers to cell **C5**, and then** the INDIRECT **address gives the value in cell **C5**.

- After that, select
**B14**and enter the function.

**=INDIRECT(ADDRESS(COLUMN(B6) – COLUMN($B$5) + ROW($B$6), ROW(B5) – ROW($B$5) + COLUMN($B$5)))**

- Next, enter the function in select
**B15**.

**=INDIRECT(ADDRESS(COLUMN(B7) – COLUMN($B$5) + ROW($B$5), ROW(B7) – ROW($B$5) + COLUMN($B$5)))**

- Then, insert the formula in cell
**B16**.

**=INDIRECT(ADDRESS(COLUMN(B8) – COLUMN($B$5) + ROW($B$5), ROW(B8) – ROW($B$5) + COLUMN($B$5)))**

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

**Method 5**

**5. Using INDEX Function**

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,

`=INDEX($B$5:$E$11,COLUMN()-1,1)`

**🔨 Formula Breakdown**

Here, the formula inserted in **B13** is

**INDEX($B$5:$E$11,COLUMN()-1,1)**

👉 **COLUMN()** returns the column number of the cell; the value inserted and is, in this case, 2. That means **COLUMN()-1,1 **returns “1,1” and **$B$5:$E$11** implies the range of cells. So, the **INDEX **function returns the value at the intersection of the first row and column of the range **B5:E11**.

- Insert the formula in cell
**B14**

`=INDEX($B$5:$E$11,COLUMN()-1,2)`

- Then in cell
**B15**Insert the formula.

`=INDEX($B$5:$E$11,COLUMN()-1,3)`

- Next, insert the formula in cell
**B16**.

`=INDEX($B$5:$E$11,COLUMN()-1,4)`

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

**Method 6**

**6. Utilizing Power Query**

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.

**Method 7**

**7. Transpose a Table & Link It to Main Data**

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.

**Method 8**

**8. Embedding VBA**

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