How to Change a Vertical Column to a Horizontal row in Excel

This is the sample dataset.

Transpose the vertical columns:

This will be the output:

how to change vertical column to horizontal in excel

Method 1 – Change a Vertical Column to a Horizontal row using the Paste Special Option in Excel

Use the Paste Special option. It keeps the exact formatting.

Select B4:C10.

Right-click.
Select Copy. You can also press Ctrl + C to copy the range.

Select a cell to paste the range horizontally. Here, B12.

Go to the Home tab and click Paste.
Select Paste Special. You can also press Ctrl + Alt + V to open the Paste Special box.

Check Transpose and click OK.

This is the output.

Note: The horizontal rows will not update dynamically.

Method 2 – Using the Excel TRANSPOSE Function to Convert a Vertical Column to a Horizontal row

Use the TRANSPOSE function. You will get dynamic updates in the horizontal rows, but rows will not keep the same formatting.

STEPS:

Select B12 and enter the formula below:

=TRANSPOSE(B4:C10)

Press Ctrl + Shift + Enter to see the result.

Method 3 – Enter the Formula as Text

STEPS:

Select B12 and enter EdB4.
Enter EdC4 in B13.

You want to see the seller in B12 and the sales amount in B13. As B4 contains seller, EdB4 was entered in B12. To see the sales amount in B13, EdC4 was used.

Select B12 and B13.
Drag the Fill Handle to the right till column H.

Select B12:H13.

Press Ctrl + H to open the Find and Replace box.
Enter Ed in “Find what” and = in “Replace with”.
Click Replace All option.

In the message box, click OK.

This is the output.

Method 4 – Swap a Vertical Column to a Horizontal row Using the INDEX Function in Excel

Use the INDEX Function. It returns the value of the cell at the intersection of a particular row and column. Add the ROW and the COLUMN functions.

STEPS:

Select B12 and enter the formula below:

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

The first argument is B4:C10 (to convert to horizontal rows). COLUMN(A1) returns the column number of A1: 1. ROW(A1) returns the row number of A1: 1. The formula becomes INDEX($B$4:$C$10,1,1). B12 will store the first value in B4:C10: Seller.

Press Enter and drag the Fill Handle down to B13.

Select B12 and B13.
Drag the Fill Handle to the right.

This is the output.

This is the dataset after formatting:

Method 5 – Applying the OFFSET Function

Use the OFFSET function. It returns a cell value that is a specific number of rows and columns from a reference. Add the ROW and the COLUMN functions.

STEPS: