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.

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

How to Transpose Multiple Columns to Rows in Excel: 6 Easy Ways

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 Paste Transpose option from the Context Menu 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.

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.

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

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

Read More: How to Change Vertical Column to Horizontal in Excel

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

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

Here B4:E12 is the array argument.

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

🔄 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

Read More: How to Flip Columns and Rows in Excel

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

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

The ret_text (i.e., “b”&COLUMN()-3) has 2 parts; 1^st one is the COLUMN function that passes the Column Number and 2^nd 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.

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

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

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.

Read More: How to Convert Multiple Rows to Columns in Excel

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.

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.

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

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.

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

Read More: How to Paste Link and Transpose in Excel

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

Step 2: Excel tends to convert the entire dataset into a Table. As a result, the Create Table dialog box appears. Click on OK.

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

➧ Choose the Transform section.

➧ Click on Transpose.

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

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

Read More: How to Move Data from Row to Column in Excel

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.

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

 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.

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

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

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: VBA to Transpose Multiple Columns into Rows in Excel

Download Excel Workbook

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

• How to Flip Data from Horizontal to Vertical in Excel
• Excel Macro: Convert Multiple Rows to Columns
• Excel VBA to Transpose Array

<< Go Back to Transpose Data in Excel | Learn Excel