How to Convert Horizontal List of Data into Vertical in Excel

This post will guide you how to transpose horizontal list to vertical list in your current worksheet in Excel. How do I convert horizontal rows to vertical columns with a formula in Excel 2013/2016.

Assuming that you have a list of data (A1:E1) in a Microsoft Excel worksheet, and you want to realize that the data you entered in rows makes better sense in columns or vice versa. You need to transpose your rows and columns from horizontal list to vertical list. This post will show you two methods to change the horizontal row style to vertical style.

Convert Horizontal List to Vertical with a Formula


If you want to convert horizontal list to a vertical list, you can create a formula based on the INDEX function in combination with ROWS function. Like this:

=INDEX(A$1:E$1, ROWS(F$1:F1))

Then enter this formula into a blank cell such as: Cell F1, and then press Enter key on your keyboard, and the drag the AutoFill Handle down to other cells, until you get a #REF! error message.

Note: Cell A1:E1 is your horizontal list, and F1 is the first cell that you want to place vertical list. It is also the cell that you want to apply for the formula.

convert horizontal list to vertical1

You would see that the horizontal list has been converted to vertical list.

 

Convert Horizontal List to Vertical using Paste Special Feature


You can also use Paste Special function to achieve the same result of transposing horizontal row data into vertical column style in Excel. Just do the following steps:

Step1: select cells that you want to convert, such as: A1:E1.

convert horizontal list to vertical2

Step2: right click on the selected cells, and select Copy option from the popup context menu.

convert horizontal list to vertical3

Step3: select a blank cell that you want to place vertical list, such as: Cell F1. And right click on it , and choose Paste Special from the popup menu list. Then the Paste Special dialog will open.

convert horizontal list to vertical4

Step4: check the Transpose box in the Paste Special dialog box. Then click Ok button.

convert horizontal list to vertical5

Step5: you would now see your horizontal data be transposed to vertical style.

convert horizontal list to vertical6

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROWS function
    The Excel ROWS function returns the number of rows in a cell reference.The syntax of the ROWS function is as below:= ROWS(array)…

Leave a Reply