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.

**Method 1**

### 1. Making Use of Built-in Features

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.

**Built-in Features**

#### 1.1. Applying Keyboard-Shortcut

The easiest way we can t**ranspose 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**

**Built-in Features**

#### 1.2. Utilizing Paste Special

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.

**Built-in Features**

#### 1.3. Using the Ribbon

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.

**Method 2**

### 2. Applying TRANSPOSE Function

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:

**TRANSPOSE(array)**

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

**=TRANSPOSE(B5:E10)**

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

**Method 3**

### 3. Employing INDIRECT Function

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:

**INDIRECT(ref_text, [a1])**

The arguments are:

**ref text** – a text-based reference

**a1 **– A boolean value that indicates whether the reference is **A1 **or** R1C1.** **TRUE**: The default style is **A1**.

**INDIRECT**

#### 3.1. Applying INDRECT with COLUMN Functions

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:

**COLUMN([reference])**

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

**=INDIRECT(“B”&COLUMN()+3)**

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

**=INDIRECT(“C”&COLUMN()+3)**

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

**=INDIRECT(“D”&COLUMN()+3)**

And for cell **B16**,

**=INDIRECT(“E”&COLUMN()+3)**

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

**🔨 Formula Breakdown **

👉 In the formula, the **COLUMN()** function returns the index number of the cell where we are writing the formula. For example, in case **B13**, the **COLUMN** function returns 2.

👉 Next, within the **INDIRECT **function, we provided the cell reference for our original data. As our starting cell was in **B5**, we typed **“B”&COLUMN()+3** as the reference. What this will do is**, INDIRECT** function will search in the **B** column, and as for the Row number, it will add 3 to 2 (returned by **COLUMN** function). Hence, collecting the ‘Fruit list’ from **B5**.

**📕 Read More: Transpose Formulas Without Changing References in Excel**

**INDIRECT**

#### 3.2. Nesting ADDRESS into INDIRECT Function

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:

**ADDRESS(row_num, col_num, [abs_num], [a1], [sheet])**

The arguments are as followed:

**row num** – the row number that will be used in the cell address.

**col num** – the column number that will be used in the cell address.

**abs_number** – the address format (i.e. absolute, relative). Absolute is the default.

**a1 **– **A1** or **R1C1** is the reference style. The default style is A1.

**sheet **– the name of the worksheet that will be used. The current sheet is the default.

Syntax of **ROW** functions:

**ROW([reference])**

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.

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

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

**🔨 Formula Breakdown**

👉 The **ADDRESS** function returns the cell address based on the row and column reference numbers we supply.

👉We input the coordinates in reverse order in our formula, and this is what actually works. In other words, this section of the formula **ADDRESS(COLUMN(B5)-COLUMN($B$5)+ROW($B$5),ROW(B5)-ROW($B$5)+COLUMN($B$5))**, flips rows to columns, converting a column number to a row number, then a row number to a column number.

👉 Finally, the rotated data is produced via the **INDIRECT** function.

**Method 4**

### 4. From Table

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.

**method 1**

### 5. Using VBA

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