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.
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
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
- (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
- COLUMNS($B:B)-1 —-> Becomes
- Output: 0
- COLUMNS($B:B)-1+(ROWS($5:5)-1)*2 —-> Turns into
- Output: 0
- 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”
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
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
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
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
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
Practice Section
Here, I’m giving you the dataset that we used in this article so that you can practice on your own.
Download Practice Workbook
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
- Excel VBA to Set Range Using Row and Column Numbers
- How to Reverse Order of Columns Vertically in Excel
- [Fixed!] Missing Row Numbers and Column Letters in Excel
- Hide Rows and Columns in Excel: Shortcut & Other Techniques
- How to Count Columns until Value Reached in Excel
