How to Transpose Multiple Columns to Rows in Excel

Often, users need to convert several columns to rows. Therefore, Excel transpose multiple columns to rows is a common operation done by users. Excel Features, TRANSPOSE, INDIRECT, and INDEX functions, Power Query, as well as VBA Macro can convert columns to rows.

Let’s say we Employee Name and Salary Data in a Worksheet. And for unavoidable reasons, we have to transpose columns to rows.

Dataset-Excel Transpose Multiple Columns to Rows

In this article, we demonstrate several ways to Excel transpose multiple columns to rows using Excel Features, Function, Power Query, and VBA Macro.


Download Excel Workbook


6 Easy Ways to Transpose Multiple Columns to Rows in Excel

Method 1: Transpose Multiple Columns to Rows Using Paste Option

Excel allows users to paste data in several formats such as Value, Formula, Transpose, only Format, etc. We can use the Context Menu Paste Transpose Options to transform columns into rows.

Step 1: Highlight the entire range that you want to transpose. Right-Click on it. The Context Menu appears. From the Context Menu Select Copy.

Paste Option-Excel Transpose Multiple Columns to Rows

Step 2: Now, select any blank cell (i.e., G4) adjacent to the range then Right-Click on it. Click on Transpose from the Paste Options.

Transpose

🔼 You can also bring up the Paste Special window using Keyboard Shortcuts CTRL+ALT+V. Afterward, choose Transpose from the Paste Special options. At last, click on OK.

Paste Special

🔼 Executing the Transpose option from Context Menu or Paste Special leads to transforming selected multiple columns into rows as depicted in the following image.

Result

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


Method 2: Transpose Columns to Rows Using TRANSPOSE Function

The TRANSPOSE function directly converts Columns to Rows and vice-versa. We can use the TRANSPOSE function no matter what the number of columns or how huge the range is. The syntax of the TRANSPOSE function is

TRANSPOSE (array)

Step 1: Use the below formula in any adjacent cell (i.e., G4) to convert columns to rows.

=TRANSPOSE(B4:E12)

Here B4:E12 is the array argument.

Transpose Function-Excel Transpose Multiple Columns to Rows

Step 2: Hit ENTER, in a moment all the columns get converted into rows.

Transpose outcome

🔄 Sometimes, the dataset contains Blank Cells and after transposing Excel inserts 0’s in them. To avoid this caveat, modify the TRANSPOSE function with the IF function to ignore blanks. In this case, the applied formula can be

=TRANSPOSE(IF(B4:E12="","",B4:E12))

Read More: How to Transpose Column to Multiple Rows in Excel (6 Methods)


Method 3: INDIRECT Function to Convert Columns to Rows

In cases where we want to establish links or fetch data from the existing range, we can use the INDIRECT function. INDIRECT function infused with the COLUMN function refers to entries from range. The syntax of the INDIRECT function is

INDIRECT (ref_text, [a1])

The statements define

ref_text; reference (as text).

a1; a boolean indication of A1 or R1C1 style cell reference. By default, it’s A1 style = TRUE. [Optional]

Step 1: Type the latter formula in any blank cell (i.e., G4).

=INDIRECT("b"&COLUMN()-3)

The ret_text (i.e., “b”&COLUMN()-3) has 2 parts; 1st one is the COLUMN function that passes the Column Number and 2nd one is the Column Name. the ref_text combines these 2 then constitutes a cell reference 3 less than the COLUMN function outcome. As a result, the final outcome becomes B(7-3) = B4. Therefore, B4 cell entry gets displayed in G4.

Indirect Function-Excel Transpose Multiple Columns to Rows

Step 2: Repeat Step 1 replacing the previous formula with the below formulas in G5, G6, and G7.

=INDIRECT("c"&COLUMN()-3)
=INDIRECT("d"&COLUMN()-3)
=INDIRECT("e"&COLUMN()-3)

All these 3 formulas declare the same arguments as the previous formula does. And they fetch the entries of their respective cells (i.e., G5=C4, G6=D4, and G7=E4).

Identical formulas

Step 3: Use the Fill Handle to apply the formulas to the right side of the cells. And all respective cells’ entries appear transposed as they are supposed to.

Result

Read More: Excel VBA: Get Row and Column Number from Cell Address (4 Methods)


Similar Readings


Method 4: Using INDEX Function to Transpose Columns to Rows

Similar to Method 3, the INDEX function brings data from a given position. The syntax of the INDEX function is

INDEX(array, row_num, [col_num])

Step 1: Write the below formula in any blank cell G4.

=INDEX($B$4:$E$14,COLUMN()-6,1)

In the formula, $B$4:$E$14 refers to the array argument. The COLUMN function returns the column number where the formula is inserted (i.e., G4). Therefore, COLUMN()-6 results (7-6) 1 as row_num for the array. And already 1 is the col_num within the array.

INDEX Function-Excel Transpose Multiple Columns to Rows

Step 2: Iterate the formulas for G5, G6, and G7 just by changing the col_num as shown in the below image.

=INDEX($B$4:$E$14,COLUMN()-6,2)
=INDEX($B$4:$E$14,COLUMN()-6,3)
=INDEX($B$4:$E$14,COLUMN()-6,4)

These formulas fetch entries from the same array, row 1 and column 2, 3, and 4 respectively. You can see that the resultant values depict the column headers as the formula fetches them.

Identical formula

Step 3: Drag the Fill Handle to insert column-wise entries horizontally. Thus transforming the columns into rows.

Outcome

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


Method 5: Transpose Multiple Columns to Rows Using Power Query

Power Query is a powerful tool to transform datasets as required. Power Query provides a Transform tab where multiple options including Transpose are available. We can use the Transpose operation to convert multiple columns to rows.

Step 1: Select the entire dataset then Go to Data > Click on From Table/Range (from Get & Transform Data section).

Power Query-Excel Transpose Multiple Columns to RowsStep 2: Excel tends to convert the entire dataset into a Table. As a result, the Create Table dialog box appears. Click on OK.

Excel Table

Step 3: In a moment, Excel loads the Power Query Editor as displayed in the below image.

➧ Choose the Transform section.

➧ Click on Transpose.

Power Query Editor

Step 4: After executing the Transpose operation, you have to load the transposed data. Move to Home > Click on Close & Load > Select Close & Load.

Load & Close

🔼 It’ll take a while to load the transposed data into a new worksheet. Eventually, Excel loads the data converting multiple columns to rows as you can observe from the below image.

Outcome

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


Method 6: Transpose Multiple Columns to Rows Using VBA Macro

VBA Macros are super-efficient in pursuing result-oriented outcomes. We can write a couple of lines of macro to transpose multiple columns to rows.

Step 1: To insert a macro, at first, open Microsoft Visual Basic using ALT+F11. Then, Select Insert (from the Toolbar) > Click on Module to insert one.

VBA-Excel Transpose Multiple Columns to Rows

Step 2: Paste the following macro in the Module.

Sub ColumnToRowTransposing()
Dim wrkRng As Range
Dim trgtRng As Range
Set wrkRng = Application.InputBox(Prompt:="Provide the columns", Type:=8)
Set trgtRng = Application.InputBox(Prompt:="Select the destination cell", Type:=8)
wrkRng.Copy
trgtRng.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub

Macro

 The macro begins its operation by taking the range to transpose and the cell to insert the transposed data using the VBA InputBox function. Then, the Paste Special method pastes the entire range as transposed data in the given cell.

Step 3: Use the F5 key to run the macro. The maco initiates the first Input Box to insert the range. Provide the range then click on OK.

Range Input

Step 4: Afterwards Excel brings the 2nd Input Box to assign the cell. Provide any convenient cell reference then Click on OK.

Destination Input

🔼 At last Excel converts the entire dataset columns to rows.

Outcome

VBA Macro can handle blank cells by offering SkipBlanks statement options in True and False. Modify the macro according to your need to better suit your data.

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


Conclusion

In this article, we demonstrate multiple features, functions, as well as VBA Macro in Excel to transpose multiple columns into rows. The TRANSPOSE function converts columns to rows in the most convenient way. Other function such as INDIRECT or INDEX also excels in their purpose. Hope these above-mentioned methods clarify your understanding of Transposing. Comment, if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo