Everyone has their own point of view when visualizing and representing data. Some may feel comfortable working with data when they are in the horizontal direction, while others may prefer to work with them when they are vertically represented. So sometimes we need to change the overview of a whole large worksheet based on the user’s need. In Excel, you can do that quickly and efficiently. This article will show you how to transpose columns to rows in Excel with the 8 most effective and easy ways.

## 8 Most Effective Methods to Transpose Columns to Rows in Excel

In the following article, we will describe the 8 most effective and easiest methods to transpose columns to rows in Excel. Here, we used Excel 365. You can use any available Excel version.

**1. Using Paste Special Feature**

The **copy-and-paste** method is the most frequently used method in Excel due to its simplicity to organize computerized documents. It is widely being used from simple modification to extensive modification in all kinds of digital tasks around the world. In this method, we will use the Copy-and-Paste method to transpose a single column to a single row.

#### 1.1. Transposing a Single Column to a Single Row

Here, we will use the** Copy and Paste** method to transpose a single column to a single row.

**Steps:**

- In the beginning, we will select cells
**B4:B10**>> press**CTRL+C**. - This will
**copy**these cells. - Here, you can
**right-click**on the selected cells and select**Copy**from the**Context Menu**.

- After that, select the cell that you want to have the first value of the row. Here, we selected cell
**B13**. - Furthermore,
**right-click**the mouse >> in the Paste Options section >> select**Transpose (T),**shown in the following picture.

- Afterward, once you select the
**Transpose (T) Paste Option**, it will transform the column into a row. - Therefore, you can see the transposed row in the following picture.

**Read More: **How to Convert Single Columns to Rows in Excel with Formulas

#### 1.2. Transposing Multiple Columns to Multiple Rows

Here, we will use the** Copy and Paste** method to transpose multiple columns to multiple rows.

**Steps:**

- In the beginning, we will select cells
**B4:C10**>> press**CTRL+C**. - This will
**copy**these cells. - Here, you can
**right-click**on the selected cells and select**Copy**from the**Context Menu**.

- After that, select the cell that you want to have the first value of the row. Here, we selected cell
**B13**. - Furthermore,
**right-click**the mouse >> in the**Paste Options**section >> select**Transpose (T),**shown in the following picture.

- Afterward, once you select the
**Transpose (T) Paste Option**, it will transform the column into a row. - Hence, you can see the transposed rows in the following picture.

**Read More: **How to Convert Multiple Columns into a Single Row in Excel

**2. Using TRANSPOSE Function**

In this method, we will use **the TRANSPOSE function** to transpose columns to rows in Excel.

#### 2.1. TRANSPOSE Function for Converting a Single Column to a Single Row

Here, we will use **the TRANSPOSE function** for a single column.

**Steps:**

- In the first place, we will type the following formula in cell
**B13**.

`=TRANSPOSE(B4:B10)`

**Formula Breakdown**

**TRANSPOSE(B4:B10)**→**the TRANSPOSE function**converts the column to a row.**B4:B10**→ is the array.

- After that, press
**ENTER**.

**Note:**If you are not using

**Excel 365**, you have to press

**CTRL+SHIFT+ENTER**since this is an array formula.

- Therefore, you will see the transposed row.

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

#### 2.2. TRANSPOSE Function for Conversion of Multiple Columns to Multiple Rows

Here, we will use **the TRANSPOSE function** to transpose multiple columns to rows in Excel.

**Steps:**

- In the beginning, we will type the following formula in cell
**B13**.

`=TRANSPOSE(B4:C8)`

- Afterward, press
**ENTER**.

**Note:**If you are not using

**Excel 365**, you have to press

**CTRL+SHIFT+ENTER**since this is an array formula.

- As a result, you will see the transposed rows.

**Read More:** Transpose Multiple Rows in Group to Columns in Excel

**3. Combining OFFSET, COLUMNS, and ROWS Functions**

In this method, we will use the combination of **OFFSET**, **COLUMNS**, and **ROWS** functions to transpose a single column to multiple rows in Excel.

**Steps:**

- In the beginning, we will type the following formula in cell
**C17**.

`=OFFSET($B$5, COLUMNS($B: B)-1+(ROWS($5:5)-1)*5,0)`

**Formula Breakdown**

**The OFFSET function**gives out a reference to a range which is a particular number of rows and columns from a cell or a number of cells.**The COLUMNS function**gives out the number of columns in a range.**The ROWS function**finds the number of rows in a range.**OFFSET($B$5,COLUMNS($B:B)-1+(ROWS($5:5)-1)*5,0)**→ becomes**Output: 1**

- At this point, press
**ENTER**. - Therefore, you can see the result in cell
**C17**. - Furthermore, we will drag the formula horizontally up to cell
**G17**using the**Fill Handle tool**.

- As a result, you can see one transposed row.
- Next, we will drag down the formula of cell
**C17**to cell**C18**using the**Fill Handle tool**. - This will give us another transposed row.

- Therefore, you can see a value in cell
**C18**. - Moreover, we will drag the formula horizontally up to cell
**G18**.

- Hence, you can see two transposed rows.

**Read More:** How to Transpose Formulas Without Changing References in Excel

**4. Applying Cell Reference to Transpose Columns to Rows in Excel**

Transposing columns to rows using cell referencing is a very efficient way while work with a large amount of data.

The steps of implementing cell referencing to transpose columns to rows are described below.

**Steps:**

- First of all, we will simply type
**saB5**in cell**C13**. - Here, we include
**sa**with the cell reference, you can type anything according to your choice. - In addition, we will drag the value horizontally up to cell
**H13**with a**Fill Handle tool**.

- Therefore, you can see the
**Odd Number**row. - Next, we will type
**saC5**in cell**C14**. - In addition, we will drag the formula horizontally with a
**Fill Handle tool**.

- Therefore, you can see two rows.

- Next, we will replace
**sa**with “**=**” so that the cells extract the value from the columns. - To do so, we will go to the
**Home**tab >> go to the**Editing**group. - Then, from
**Find & Select**>> select**Replace**.

- At this point, a
**Find and Replace****dialog box**will appear. - Afterward, in the
**Find what**box, type.*sa* - Moreover, in the
**Replace with**box, type*=* - Along with that, click on
**Replace All**.

- At this point, a confirmation dialog box will pop up.
- Click
**OK**.

- After that, close the
**Find and Replace**dialog box. - Therefore, you can see that rows now have the values of the columns.
- Hence, you can transpose columns to rows.

**Read More: **How to Transpose Rows to Columns Based on Criteria in Excel

### 5. Merging INDIRECT and COLUMN Functions to Transpose Columns to Rows in Excel

In this method, we will use the combination of **INDIRECT** and **COLUMN** functions to **transpose columns to rows in Excel**.

**Steps:**

- In the first place, we will type the following formula in cell
**F4**.

`=INDIRECT("b"&COLUMN()-1)`

**Formula Breakdown**

**The INDIRECT function**finds the reference to a particular text string.**The COLUMN function**finds the column number of a particular cell reference.**INDIRECT(“b”&COLUMN()-1)**→becomes**Output: 1**

- At this moment, press
**ENTER**. - Therefore, you can see the result in cell
**F4**. - Furthermore, we will drag the formula horizontally up to cell
**I4**with the**Fill Handle tool**.

- Therefore, you can see the complete
**Odd Number**row.

- After that, we will type the following formula in cell
**F5**.

`=INDIRECT("c"&COLUMN()-1)`

- At this moment, press
**ENTER**. - Therefore, you can see the result in cell
**F5**. - Furthermore, we will drag the formula horizontally up to cell
**I5**with the**Fill Handle tool**.

- As a result, you can see the transposed rows.

**Read More:** How to Reverse Transpose in Excel

### 6. Combining INDEX and COLUMN Functions to Transpose Columns to Rows in Excel

In this method, we will merge the **INDEX** and **COLUMN** functions to **transpose columns to rows in Excel**.

**Steps:**

- In the beginning, we will type the following formula in cell
**F4**.

`=INDEX($B$4:$C$8, COLUMN()-4,1)`

** **

**Formula Breakdown**

**The INDEX function**finds a value with a table or range.**The COLUMN function**finds the column number of a particular cell reference.**INDEX($B$4:$C$8,COLUMN()-4,1)**→ becomes**Output:1**

**ENTER**.- Therefore, you can see the result in cell
**F4**. - Furthermore, we will drag the formula horizontally up to cell
**I4**with the**Fill Handle tool**.

**Odd Number**row.- Afterward, we will type the following formula in cell
**F5**.

`=INDEX($B$4:$C$8,COLUMN()-4,1)`

- At this point, press
**ENTER**. - As a result, you can see the result in cell
**F5**. - Moreover, we will drag the formula horizontally up to cell
**I5**with the**Fill Handle tool**.

- Hence, you can see two transposed rows.

**Read More:** How to Reverse Order of Columns Horizontally in Excel

### 7. Use of Power Query to Transpose Columns to Rows in Excel

In this method, we will use the** Power Query **to transpose columns to rows in Excel.

**Steps:**

- In the first place, we will select the entire dataset by selecting cells
**B4:C11**. - After that go to the
**Data**tab >> select**From Table/Range**.

- At this moment, a
**Create Table**dialog box will appear. - Here, make sure
**My table has headers**marked. - Then, click
**OK**.

- Therefore, you can see the created
**Power Query**of the dataset. - Then, we will go to the
**Transform**tab >> select**Transpose**.

- Therefore, you can see the transposed rows in the
**Power Query**.

- Now, it is time to shift the transposed dataset to our
**Worksheet**. - To do so, we will go to the
**Home**tab. - Then, from the
**Close & Load**group >> select**Close & Load To**.

- At this point, an
**Import Data**dialog box will pop up. - Here, we want to import data in the existing worksheet, therefore, we select
**Existing Worksheet**. - Moreover, we select cell
**B14**for the location. - Then, click
**OK**.

- Hence, you can see the transposed rows in the existing worksheet.

**Read More:** Convert Columns to Rows in Excel Using Power Query

### 8. Inserting VBA Macro to Transpose Columns to Rows in Excel

This is a quick and handy procedure to transpose columns to rows in Excel.

**Steps:**

- First, go to the
**Develope**r tab >> select**Visual Basic**. - This will bring out a
**VBA Editor**window. - You can also press
**ALT+F11**to bring out the**VBA****Editor**window.

At this point, a **VBA Editor** window will appear.

Then, from the** Insert** tab >> select **Module**.

- Furthermore, we will type the following code in the
**Module**.

```
Sub Transpose_Columns_to_Rows()
Dim work_range As Range
Dim target_range As Range
Set work_range = Application.InputBox _
(Prompt:="Select the columns", Type:=8)
Set target_range = Application.InputBox _
(Prompt:="Select location cell", Type:=8)
work_range.Copy
target_range.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub
```

- Then,
**Save**the Code. - Now, return to the Worksheet, go to the
**Developer**tab >> select**Macro**.

- Next, select the
**Sub-procedure**of the code >> click on**Run**.

- Therefore, an
**Input**window will appear. - Then, we will select columns
**B4:B9**in the**Select the column**box >> click**OK**.

- In addition, another
**Input**window will appear. - Here, we will select cell
**B12**in the**Select the location**box >> click**OK**.

- Therefore, you can see a transposed row in cell
**B12:G12**.

- Furthermore, we again run the code for the
**Even Number**column. - Therefore, you can see two transposed rows.

**Read More:** VBA to Transpose Multiple Columns into Rows in Excel

**Things to Remember**

- While using the Transpose function, if you are not using Excel 365, then
**don’t hit only**, hit*ENTER*.*CTRL + SHIFT+ ENTER* - Remember
**the Transpose function**is not working with just a single data, it is working with a large amount of data so**don’t forget to select a range of cells**while transposing your dataset.

## Do It Yourself

You can download the above Excel file and practice the explained methods.

**Download Practice Workbook**

You can download the practice workbook from here.

**Conclusion**

Here, we show you 8 easy methods to transpose columns to rows in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.