How to Change Vertical Column to Horizontal in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

how to change vertical column to horizontal in excel


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.

Change Vertical Column to Horizontal with Paste Special Option in Excel

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

Change Vertical Column to Horizontal with Paste Special Option in Excel

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

Change Vertical Column to Horizontal with Paste Special Option in Excel

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

Change Vertical Column to Horizontal with Paste Special Option in Excel

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

Change Vertical Column to Horizontal with Paste Special Option in Excel

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

Change Vertical Column to Horizontal with Paste Special Option in Excel

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.

STEPS:

  • In the first place, select Cell B12 and type the formula below:
=TRANSPOSE(B4:C10)

Insert Excel TRANSPOSE Function to Convert Vertical Column to Horizontal

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

Insert Excel TRANSPOSE Function to Convert Vertical Column to Horizontal

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

Read More: How to Transpose Multiple Columns to Rows in Excel


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.

Type Formula as Text to Get Vertical Column into Horizontal

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.

Type Formula as Text to Get Vertical Column into Horizontal

  • After that, select the range B12:H13.

Type Formula as Text to Get Vertical Column into Horizontal

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

Type Formula as Text to Get Vertical Column into Horizontal

  • A message box will appear.
  • Click OK to proceed.

Type Formula as Text to Get Vertical Column into Horizontal

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

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


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))

Swap Vertical Column to Horizontal Using INDEX Function in Excel

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.

Swap Vertical Column to Horizontal Using INDEX Function in Excel

  • Now, select cell B12 and B13.
  • Then, drag the Fill Handle to the right.

Swap Vertical Column to Horizontal Using INDEX Function in Excel

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

Swap Vertical Column to Horizontal Using INDEX Function in Excel

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

Read More: How to Flip Data from Horizontal to Vertical in Excel


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)

Apply OFFSET Function to Switch Vertical Column

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.

Apply OFFSET Function to Switch Vertical Column

  • After that, select cell B12 and B13.
  • Now, drag the Fill Handle to the right till Column H.

Apply OFFSET Function to Switch Vertical Column

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

STEPS:

  • In the first place, type the formula below in cell C12:
=INDIRECT("B"&COLUMN()+1)

Transpose Vertical Column to Horizontal Using INDIRECT Function

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.

Read More: How to Paste Link and Transpose in Excel


Download Practice Workbook

You can download the workbook from here.


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. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles


<< Go Back to Transpose Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo