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.
Download Excel Workbook
6 Easy Ways to Transpose Multiple Columns to Rows in Excel
Method 1: Transpose Multiple Columns to Rows Using Paste Option
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.
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
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).
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.
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.
- How to Switch Rows and Columns in Excel Chart (2 Methods)
- [Fixed!] Rows and Columns Are Both Numbers in Excel
- How to Add Multiple Rows and Columns in Excel (Every Possible Way)
- Excel VBA: Set Range by Row and Column Number (3 Examples)
- How to Add Rows and Columns in Excel (3 Easy Methods)
Method 4: Using INDEX Function to Transpose Columns to Rows
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.
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.
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 2nd 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.
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.
- Excel VBA to Set Range Using Row and Column Numbers (4 Examples)
- How to Hide Rows and Columns in Excel (10 Ways)
- [Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)
- Hide Rows and Columns in Excel: Shortcut & Other Techniques
- How to Freeze Rows and Columns at the Same Time in Excel