If you are looking for **the Transpose feature** to convert columns to rows or the inverse one in Excel, then you have come to the right place. In this article, we’re going to show you how to **convert columns to rows** in Excel.

**Table of Contents**hide

## Download practice workbook

You can download the Practice Workbook that we used to prepare this article. Furthermore, you can change or modify data & find new outputs accordingly.

## 3 Methods to Convert Columns to Rows in Excel

Here, we will describe** 3 **methods to **convert columns to rows** in Excel. In addition, for your better understanding, we’re going to use the following dataset. Which contains **5** columns. They are **Serial No, Products, Volume, Unit Cost,** and **Revenue**.

### 1. Transposing Data to Convert Columns to Rows

Here, we have the dataset. The data shows Sales by Month in different countries. Now, we will be **converting the columns to rows**.

#### 1.1. Use of Context Menu Bar

Here, we will use the **Paste Options** from the **Context Menu Bar** to convert **columns to rows** in Excel. Actually, this is the easiest option for converting **columns to rows**. Eventually, you can also **convert rows to columns **with this feature in Excel. Now, let’s see the steps given below.

- Firstly, select the whole dataset >> Right-Click on the dataset.
- Secondly, from the
**Context Menu Bar**>> Click on the**Copy**button.*Or, after selecting the dataset, press***CTRL+C**on the keyboard to copy the dataset.

As a result, the dataset will be highlighted.

- Now, select the
**B11**cell where we want to keep the output. - Then,
**Right-Click**on the cell >> from the**Context Menu Bar**>> choose**Transpose (T)**which is under the**Paste Options**.

Lastly, you will see the following image.

**Read More: ****VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)**

#### 1.2. Applying Paste Special Feature

Here, we will use the **Paste Special** feature to convert **columns to rows** in Excel. Now, let’s see the steps given below.

**Steps:**

- Firstly, select the whole dataset.
- Secondly, from the
**Home**Ribbon >> you may click on the**Copy**command which is under the**Clipboard**group of commands.

As a result, the dataset will be copied.

- Now, select the
**B11**cell where we want to keep the output. - Then, from the
**Home**tab >> go to**Paste**command. - Then, choose
**Paste Special**option.

At this time, a new dialog box named **Paste Special** will appear.

- Now, mark
**Transpose**. - After that, press
**OK**.

Lastly, you will see the converted rows.

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

### 2. Use of INDIRECT & ADDRESS Functions in Excel

Now, you can use a** combination **of some functions to **convert columns to rows** in Excel. Here, we will use **INDIRECT****, ****ADDRESS****, ****COLUMN****, **and **ROW** functions.

**Steps:**

- Firstly, you must select a cell, where you want to keep the result. Here, we have selected the
**B11**cell. - Secondly, you need to use the corresponding formula in the
**B11**cell.

`=INDIRECT(ADDRESS(COLUMN(B4) - COLUMN($B$4) + ROW($B$4), ROW(B4) - ROW($B$4) + COLUMN($B$4)))`

- Thirdly, press
**ENTER**.

**Formula Breakdown**

- Firstly,
**COLUMN(B4)—>**returns the column number from a given reference.**Output: {2}.**

- Secondly,
**ROW(B4)—>**returns the row number from a given reference.**Output: {4}.**

- Thirdly,
**ROW(B4) – ROW($B$4) + COLUMN($B$4)—>**becomes**{4}-{4}+{2}.**Here, the**Dollar Sign ($)**will fix column**B**.**Output: {2}.**

- Fourthly,
**COLUMN(B4) – COLUMN($B$4) + ROW($B$4)—>**becomes**{2}-{2}+{4}.**Here, the**Dollar Sign ($)**will fix column**B**.**Output: {4}.**

- So, the
**ADDRESS**function will return the position of row number**4**and column number**2.****Output: {“$B$4”}.**

- Finally, the
**INDIRECT**function will return the value of that cell.**Output: SL No.**

- After that, you have to drag the
**Fill Handle**icon to**AutoFill**the corresponding data in cells**B12:B15**.

- Similarly, you have to drag the
**Fill Handle**icon to**AutoFill**the corresponding data in cells**C12:G15**.

Lastly, you will see the converted rows.

**Read More: How to Transpose Duplicate Rows to Columns in Excel (4 Ways)**

**Similar Readings**

**How to Perform Conditional Transpose in Excel (2 Easy Examples)****Convert Column to Comma Separated List With Single Quotes****How to Transpose Multiple Columns into One Column in Excel****Transpose a Table in Excel (5 Suitable Methods)****How to Transpose Data in Excel (4 Easy Ways)**

### 3. Using TRANSPOSE Function to Convert Columns to Rows

Sometimes you may want to rotate cells. Actually, you may do this by the previous methods, and also by using the **VBA** with **Transpose** option. But that might create matched values. So, in this section we will see another way, you can use a formula using **the TRANSPOSE function**. To explain the method, we will consider the dataset used in the previous formula.

**Steps:**

- Firstly, select a blank cell. Here, we select the
**B11**cell. Count the number of cells in**B4:F9**. In this case, it is**30**cells. - Secondly, type the range of the cells you want to transpose. In this example, we want to transpose cells from
**B4 to F9**. So the formula for this example would be:

`=TRANSPOSE(B4:F9)`

- Then, press
**ENTER**.

The formula will be applied to more than one cell. Here’s the result after pressing **ENTER:**

**Read More: ****How to Convert Multiple Columns into a Single Row in Excel (2 Ways)**

## How to Solve TRANSPOSE Function & SPILL Errors in Excel

The **Transpose** formula version is bound to the original data. So, for example, if we change the value in **D5** from **14** to **2**, the new value will automatically be updated. But the reverse isn’t true.

If we change any transposed cells, the original set will not change. Instead, we will get a **SPILL **error and the **transposed data** will disappear.

## How to Use TRANSPOSE Function with Blank Cells in Excel

The **TRANSPOSE** function will convert the empty cells to **“0”**s. Follow the image below, you will get the scenario.

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

## How to Convert Rows to Columns in Excel

Here, you can also convert** rows to columns** in Excel. Let’s have the following dataset. Where the information is row-wise decorated. Now, we want to make a dataset that will be column-wise decorated.

Here comes the most interesting part, you can follow **method-1** to convert the** rows to columns**. Actually, you can follow any of the methods. As the **method 1** is the easiest one so we have mentioned it. After following the steps, you will get the following converted dataset.

**Read More: ****How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)**

## Practice Section

Now you can practice the explained methods by yourself.

## Things to Remember

Here, the process of converting **columns to rows **or **vice-versa** all those methods also works when you want to convert a single column to a row or vice-versa.

## Conclusion

So, these are the **three **easy methods how to** convert columns to rows** in Excel. You can apply any of these** three **methods. So, we hope you find this article useful. Furthermore, feel free to share your thoughts in the comment section.

## Further Readings

**How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)****Excel VBA: Transpose Multiple Rows in Group to Columns****How to Change Vertical Column to Horizontal in Excel****Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)****How to Reverse Order of Columns Horizontally in Excel****Excel VBA to Transpose Array (3 Suitable Examples)****How to Swap Rows in Excel (4 Easy Ways)**