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

**Table of Contents**hide

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

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

**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 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; **1 ^{st}** one is the

**COLUMN**

**function**that passes the

**Column Number**and

**2**one is the

^{nd}**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: Excel VBA: Get Row and Column Number from Cell Address (4 Methods)**

**Similar Readings**

**How to Switch Rows and Columns in Excel Chart (2 Methods)****Add Multiple Rows and Columns in Excel (Every Possible Way)****How to Skip Every Other Column Using Excel Formula (3 Methods)****Excel VBA: Set Range by Row and Column Number (3 Examples)****How to Find Column Number Based on Value 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 Find Column Index Number in Excel (2 Methods)**

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

**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: 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 I**NDIRECT** 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

**Excel VBA to Set Range Using Row and Column Numbers (4 Examples)****How to Count Columns until Value Reached in Excel****Hide Rows and Columns in Excel: Shortcut & Other Techniques****How to Freeze Rows and Columns at the Same Time in Excel****Move Row/Column in Excel Without Replacing Existing Data (3 Best Ways)****How to Reverse Order of Columns Vertically in Excel (3 Ways)**