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

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

**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

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

**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

`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; 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**.

`=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**).

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

`=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.

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

**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**