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.
How to Change Vertical Column to Horizontal in Excel: 6 Easy Ways
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.
- 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.
Read More: How to Flip Columns and Rows in Excel
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.
- In the first place, select Cell B12 and type the formula below:
- 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.
- 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.
- Firstly, select cell B12 and type the formula below:
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.
- First of all, select cell B12 and type the formula below:
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.
Read More: How to Move Data from Row to Column in Excel
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.
- In the first place, type the formula below in cell C12:
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:
- 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.
Read More: How to Paste Link and Transpose in Excel
Download Practice Workbook
You can download the workbook from here.
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. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
- Excel VBA to Transpose Array
- Excel Macro: Convert Multiple Rows to Columns
- VBA to Transpose Multiple Columns into Rows in Excel