The article will show you some easy methods on how to **transpose column(s) **to **multiple rows **in Microsoft Excel. Sometimes, we may need to change our dataset’s view for convenience, so we may have to **transpose columns **to **multiple rows**.

We are going to use a dataset where we have information about some members of an electronic shop containing their **Customer ID**, **Name**, and the **Products **they bought.

**Table of Contents**hide

## Download Practice Workbook

## 6 Ways to Transpose Column to Multiple Rows in Excel

**1. Using Excel TRANSPOSE Function to Transpose Column to Multiple Rows**

The most common way to **transpose columns **to **multiple rows **is to use **the TRANSPOSE Function**. We are going to **transpose **all the **columns **of our dataset to **multiple rows**. Let’s see the discussion below.

**Steps:**

- Type the following formula in cell
**B12**.

`=TRANSPOSE(B4:D10)`

Here, **the TRANSPOSE Function** returns the **transpose **of the **array B4:D10**, meaning it will convert the **columns **and **rows **of **B4:D10 **to **rows **and **columns **respectively.

- Press the
**ENTER**button and you will see this operation will**transpose**the whole dataset.

Thus you can easily **transpose columns **to **multiple rows **simply by using **the TRANSPOSE Function**.

**Read More: Excel Macro: Convert Multiple Rows to Columns (3 Examples)**

**2. Applying Combination of Functions to Transpose Column to Multiple Rows in Excel**

We can turn a **single column **into **multiple rows **by combining **OFFSET**, **COLUMNS**, and **ROWS**** Functions**. Suppose you want to view the **Customer IDs **as a **3×2 Matrix**. Let’s have a look at the following description.

**Steps:**

- Make a heading for
**Customer ID**and type the following formula in cell**C12**.

`=OFFSET($B$5,COLUMNS($B:B)-1+(ROWS($5:5)-1)*2,0)`

**Formula Breakdown**

Here, we used **the** OFFSET Function to return values taking the **B5 **cell as a reference. It will return a **range **that is a particular number of columns and rows from the reference **B5**.

**ROWS($5:5)-1 —->**Returns- Output:
**0**

- Output:
**(ROWS($5:5)-1)*2 —->**Also returns (We multiply this formula by**2**because the return array will be a**3×2 Matrix**and we want**2**cells in each**row**)- Output:
**0**

- Output:
**COLUMNS($B:B)-1 —->**Becomes- Output:
**0**

- Output:
**COLUMNS($B:B)-1+(ROWS($5:5)-1)*2 —->**Turns into- Output:
**0**

- Output:
**OFFSET($B$5,COLUMNS($B:B)-1+(ROWS($5:5)-1)*2,0) —->**Simplifies to**OFFSET($B$5,0,0) —->**Here, the**OFFSET**function offsets**B5**Offsets**0 row**and**0 column**and will return the value of the**B5**cell.- Output:
**“012A”**

- Output:

Finally, we get the cell value of **B5**.

- Hit
**ENTER**and you will see the first**Customer ID**in this cell.

- Now use the
**Fill Handle**to**AutoFill**the adjacent cell**D12**.

- Then, drag the
**Fill Handle icon**vertically to cell**D14**and you will see the**Customer IDs**in a**3×2 Matrix**

Thus you can **transpose **a **single column **into **multiple rows **by combining **OFFSET**, **ROWS**, and **COLUMN Functions**.

**Read More: How to Convert Multiple Rows to Columns in Excel (9 Ways)**

**3. Implementing Excel INDEX Function to Transpose Column to Multiple Rows**

We can also apply **the INDEX Function** to **transpose columns **to **multiple rows**. I’m giving you the necessary instructions below.

**Steps:**

- Type the following formula in cell
**B12**.

`=INDEX($B$3:$D$10,COLUMN(B1),1)`

Here we define **the COLUMN Function **as the **row_number **for **the INDEX Function **as we want to make the **columns **as **rows**. The **COLUMN(B1) **formula will return the **Column number **of that cell reference which is **2** (You may use any cell reference from **Column B**). Then **INDEX** looks in the **range $B$3:$D$10 **and returns the value which is in the **2nd row **and **1st column **of the **range $B$3:$D$10**. In this case, the output value will be **Customer ID**.

- Hit
**ENTER**and you will see the**Customer ID**header appear in cell**B12**.

- After that type, the following formula similarly in cell
**B13**and hit**ENTER**to see the**Name**

`=INDEX($B$3:$D$10,COLUMN(B1),2)`

- Then type the following formula in cell
**B14**and press**ENTER**to see the**Product**header in it in the same way.

`=INDEX($B$3:$D$10,COLUMN(B1),3)`

- Now select
**B12:B14**and drag the**Fill Handle icon**horizontally to**H14**. You will see the whole dataset becomes**transposed**.

Thus you can **transpose columns **to **multiple rows **by using **INDEX **and **COLUMN Functions**.

**Read More: How to Transpose Multiple Columns to Rows in Excel**

**Similar Readings**

**How to Add Multiple Rows and Columns in Excel (Every Possible Way)****Excel VBA: Get Row and Column Number from Cell Address (4 Methods)****How to Add Rows and Columns in Excel (3 Easy Methods)****Excel VBA: Set Range by Row and Column Number (3 Examples)****How to Find Column Number Based on Value in Excel**

**4. Transposing Column to Multiple Rows by Excel INDIRECT Function**

We can also apply **the INDIRECT Function** to **transpose columns **to **multiple rows**. I’m giving you the necessary instructions below.

**Steps:**

- Type the following formula in cell
**B12**.

`=INDIRECT("B"&COLUMN(D5))`

Basically, we want to make **Columns B**, **C**, and **D **of the dataset as **rows**. So we put a reference in **the INDIRECT Function**. The **COLUMN(D5) **formula will return the **column number **of this cell reference which is **4**. Our dataset also starts at the **4th row**. You may use any of the cell references from **column D **for **the COLUMN Function**. As a result, we get the cell value of **B4 **which is **Customer ID**.

- Hit
**ENTER**and you will see the**Customer ID**header appear in cell**B12**.

- After that type, the following formula similarly in cell
**B13**and hit**ENTER**to see the**Name**

`=INDIRECT("C"&COLUMN(D5))`

- Then type the following formula in cell
**B14**and press**ENTER**to see the**Product**header in it in the same way.

`=INDIRECT("D"&COLUMN(D5))`

- Now select
**B12:B14**and drag the**Fill Handle icon**horizontally to**H14**. You will see the whole dataset becomes**transposed**.

Thus you can **transpose columns **to **multiple rows **by using **INDIRECT **and **COLUMN Functions**.

**Read More:** **How to Switch Rows and Columns in Excel Chart (2 Methods)**

**5. Utilizing Cell Reference to Transpose Column to Multiple Rows**

Another way to **transpose columns **to **multiple rows **is to use **cell references**. Let’s go through the description below.

**Steps:**

- Select any empty cell from where you want to
**transpose**your dataset. In this case, I selected**B12**and typed**TransB4**instead of the formula ‘**=B4**’. Here**Trans**is a homemade prefix.

- Similarly type
**TransC4**and**TransD4**in cells**B13**and**B14**

- Now select
**B12:B14**and drag the**Fill Handle icon**horizontally up to**H14**.

- After that, go to
**Home**>>**Find & Select**>>**Replace**.

- A
**dialog box**will show up. You want cell values in the**transposed**So you need to replace ‘**Trans**’ with the**EQUAL**symbol (‘**=**’). So type**Trans**in**Find what**section and ‘**=**’ in**Replace with**section. - Click on
**Replace All**.

- Then a
**message box**will show up showing how many changes this operation made. Just click**OK**on it.

**Close**the**Find and Replace dialog box**.

Thus you can **transpose columns **to **multiple rows **by using **cell reference**.

**Read More: How to Switch Rows and Columns in Excel (5 Methods)**

**6. Using VBA to Transpose Column to Multiple Rows**

We can also apply **Microsoft Visual Basic for Application** **(VBA)** to **transpose **a **single column **into **multiple rows**. Suppose you want to view the **Customer IDs **as a **3×2 Matrix**. Let’s go through the procedure below.

**Steps:**

- First, open
**Visual Basic**from the**Developer Tab**.

- The
**VBA Window**will open. Select**Insert**>>**Module**.

- Type the following code in the
**VBA Module**.

```
Sub TransposeToColumn()
steps = 2
FR = 5
FC = 2
New_FC = 3
New_FR = 12
New_Current_Column = New_FC
New_Current_Row = New_FR
LR = Cells(Rows.Count, FC).End(xlUp).Row
For CR = FR To LR
Cells(New_Current_Row, New_Current_Column).Value = Cells(CR, FC).Value
New_Current_Column = New_Current_Column + 1
If (CR - (FR - 1)) Mod steps = 0 Then
New_Current_Column = New_FC
New_Current_Row = New_Current_Row + 1
End If
Next
End Sub
```

**Code Breakdown**

- We define
**steps**equal to**2**as we want**2**cells in each row. - Our first value is in cell
**B5**so we define**First Row (FR)**as**5**and**First Column (FC)**as**2**. - We will store the
**values**in cell**C12**so we define**New First Column (New_FC)**as**3**and**New First Row (New_FR)**as**12**. - We also define
**Last Row (LR)**to keep the last used row number. - Next, we used a
**For Loop**to go through from**FR row 5 to LR, row****10**. - Then kept the values of the previous cell in the new
**New_Current_Row**and**New_Current_Column**. Increment the**New_Current_Column**value by 1. - Then, the VBA Mod operator was used within
**the IF statement**to**transpose**the rest of the values. Also incremented**New_Current_Row**by 1.

- Go back to your sheet and
**Run**Macros.

- After that, you will see the
**Customer IDs**in a**3×2 Matrix**.

- Put a header before the
**IDs**for convenience.

By following this approach, you can **transpose **a **single column **to **multiple rows**.

**Read More:** **Move Row/Column in Excel Without Replacing Existing Data (3 Best Ways)**

## Practice Section

Here, I’m giving you the dataset that we used in this article so that you can practice on your own.

## Conclusion

In the end, by following the path that’s been made in this article, you can learn some basic methods on how to **transpose columns **to **multiple rows **in Excel. We have used various functions and **VBA **to show these methods. I hope you will benefit from my article. If you have any easier methods or feedback, please feel free to share them in the comment box. This will help me enrich my upcoming articles.

## Related Articles

**How to Find Column Index Number in Excel (2 Methods)****Excel VBA to Set Range Using Row and Column Numbers (4 Examples)****How to Reverse Order of Columns Vertically in Excel (3 Ways)****[Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)****Hide Rows and Columns in Excel: Shortcut & Other Techniques****How to Count Columns until Value Reached in Excel**