In this article, we will learn to **change a vertical column to a horizontal row in Excel**. Microsoft Excel is a powerful tool and it helps users to perform different tasks easily. Users create datasets to represent data. The datasets consist of rows and columns. Sometimes, users may need to change vertical columns to horizontal ones in Excel. Today, we will demonstrate **5 **different methods. Using these methods, you can easily change a vertical column to a horizontal row in Excel. So, without further delay, let’s start the discussion.

**Table of Contents**hide

## Download Practice Workbook

You can download the workbook from here.

## 6 Easy Ways to Change Vertical Column to Horizontal in Excel

To explain the methods, we will use a dataset that contains the **Sales Amount **of some **Sellers**. We will swap the vertical columns into horizontal rows in today’s article. You can say we will transpose the vertical columns.

After changing vertical columns to horizontal ones, the dataset will look like the picture below.

### 1. Change Vertical Column to Horizontal with Paste Special Option in Excel

The easiest way to change a vertical column to a horizontal row is to use the **Paste Special **option of Excel. It also keeps the exact formatting while changing the vertical column. So, you don’t need to apply any formatting later. Let’s follow the steps below to see how we can use the **Paste Special **option to transpose the columns.

**STEPS:**

- First of all, select the range that you want to change to horizontal rows. Here, we have selected the
**range B4:C10**.

- Secondly,
**right**–**click**on the mouse to open the menu. - Select
**Copy**from there. - Alternatively, you can press
**Ctrl**+**C**to copy the range.

- Thirdly, select a cell where you want to paste the range horizontally. In our case, we have selected
**Cell B12**.

- After that, go to the
**Home**tab and click on the**Paste**icon. A drop-down menu will appear. - Select
**Paste Special**from there. It will open the**Paste Special**box. - Or, you can press
**Ctrl**+**Alt**+**V**to open the**Paste Special**box.

- In the
**Paste Special**box, check the**Transpose**option and click**OK**to proceed.

- Finally, you will be able to change the vertical columns to horizontal rows.

**Note: **As stated earlier, this method can be implemented effortlessly. But it has a drawback. The horizontal rows will not update dynamically if you change anything in the vertical columns. If you want dynamic updates, then it is better to follow the other methods.

### 2. Insert Excel TRANSPOSE Function to Convert Vertical Column to Horizontal

We can use some **Excel Functions** to convert a vertical column to a horizontal row. Here, we will use **the TRANSPOSE function** for that purpose. The main advantage of using functions is that you will get dynamic updates in the horizontal rows if you change anything in the main dataset. But, the horizontal rows will not have the same formatting as the vertical columns. You need to add formatting after applying the method.

Let’s follow the steps below to learn more.

**STEPS:**

- In the first place, select
**Cell B12**and type the formula below:

`=TRANSPOSE(B4:C10)`

- After that, press
**Ctrl**+**Shift**+**Enter**to get results like the picture below.

**Note: **You can see there is no formatting in the converted rows. You need to apply the formatting again.

### 3. Type Formula as Text to Get Vertical Column into Horizontal

Another way to get vertical columns into horizontal rows is to type the formula as text. This is one of the most interesting tricks you will ever see. Here, we will type the formula with some special letters first. Later, we will replace them with the equal sign. Let’s pay attention to the steps below to see the process of typing formulas as texts.

**STEPS:**

- In the beginning, select
**Cell B12**and type**EdB4**. - Also, type
**EdC4**in**Cell B13**.

Here, we want to see the **Seller **in **Cell B12 **and **Sales Amount **in **Cell B13**. As **Cell B4 **contains **Seller**, that is why we have typed **EdB4 **in **Cell B12**. To see the **Sales Amount **in **Cell B13**, we have typed **EdC4**.

- Now, select both
**Cell B12**and**Cell B13**. - Then, drag the
**Fill Handle**to the right till**Column H**.

- After that, select the
**range B12:H13**.

- In the following step, press
**Ctrl**+**H**to open the**Find and Replace**box. - In the
**Find and Replace**box, type**Ed**in the “**Find what**” field and**=**in the “**Replace with**” field. - After typing them, click on the
**Replace All**option.

- A message box will appear.
- Click
**OK**to proceed.

- Finally, you will see results like the picture below.

### 4. Swap Vertical Column to Horizontal Using INDEX Function in Excel

We can also change vertical columns to horizontal rows using **the INDEX Function** in Excel. The **INDEX **function returns a value of the cell at the intersection of a particular row and column. To complete the formula, we will use the **ROW** and **COLUMN** functions. Let’s follow the steps below to learn more.

**STEPS:**

- Firstly, select
**Cell B12**and type the formula below:

`=INDEX($B$4:$C$10,COLUMN(A1),ROW(A1))`

Here, the first argument is the **range B4:C10**. We need to convert it to horizontal rows. **COLUMN(A1) **returns the column number of **Cell A1 **and that is **1**. Also, **ROW(A1) **returns the row number of **Cell A1 **which is **1**. So, the formula becomes **INDEX($B$4:$C$10,1,1)**. It means **Cell B12 **will store the first value of the **range B4:C10 **which is **Seller**.

- Secondly, press
**Enter**and drag the**Fill Handle**down to**Cell B13**.

- Now, select
**Cell B12**and**B13**. - Then, drag the
**Fill Handle**to the right.

- As a result, you will be able to change the vertical columns to horizontal rows.

- Finally, after applying proper formatting the dataset will look like the picture below.

### 5. Apply OFFSET Function to Switch Vertical Column

Among the functions, we can also use **the OFFSET function** to switch vertical columns to horizontal rows in Excel. The **OFFSET **function returns a cell value that is a particular number of rows and columns from the reference. Here, we will again need to use the **ROW **and **COLUMN **functions. Let’s follow the steps below to learn more.

**STEPS:**

- First of all, select
**Cell B12**and type the formula below:

`=OFFSET($B$4,COLUMN(A1)-1,ROW(A1)-1)`

Here, inside the **OFFSET **function **Cell B4 **is the reference. **COLUMN(A1)-1 **and **ROW(A1)-1 **denote the row and column numbers from the reference respectively.

- Secondly, hit
**Enter**and drag the**Fill Handle**down.

- After that, select
**Cell B12**and**B13**. - Now, drag the
**Fill Handle**to the right till**Column H**.

- As a result, you will get the vertical columns as horizontal rows.

- Lastly, apply formatting to make the horizontal rows like the vertical columns.

### 6. Transpose Vertical Column to Horizontal Using INDIRECT Function

In the last method, we will use **the INDIRECT function** to transpose vertical columns. This method is similar to the previous one. The **INDIRECT **function returns the reference specified by a text string. Let’s observe the steps below to see how we can implement the method.

**STEPS:**

- In the first place, type the formula below in
**Cell C12**:

`=INDIRECT("B"&COLUMN()+1)`

Here, the output of **COLUMN() **is **3**. So, the formula becomes **INDIRECT(B4)**. That is why it returns **Seller **in **Cell C12**.

- Then, press
**Enter**and type the formula below:

`=INDIRECT("C"&COLUMN()+1)`

- After that, drag the
**Fill Handle**to the right till**Column I**.

- As a result, you will be able to transpose the vertical columns like the picture below.

## Conclusion

In this article, we have **6 **easy methods to **Change a Vertical Column to a Horizontal row in Excel**. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit **the ExcelDemy website** for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.