How to Change a Vertical Column to a Horizontal row in Excel – 6 Methods

This is the sample dataset.

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

  • Select B4:C10.

Change Vertical Column to Horizontal with Paste Special Option in Excel

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

Change Vertical Column to Horizontal with Paste Special Option in Excel

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

Change Vertical Column to Horizontal with Paste Special Option in Excel

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

Change Vertical Column to Horizontal with Paste Special Option in Excel

  • Check Transpose and click OK.

Change Vertical Column to Horizontal with Paste Special Option in Excel

This is the output.

Change Vertical Column to Horizontal with Paste Special Option in Excel

Note: The horizontal rows will not update dynamically.


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

STEPS:

  • Select B12 and enter the formula below:
=TRANSPOSE(B4:C10)

Insert Excel TRANSPOSE Function to Convert Vertical Column to Horizontal

  • Press Ctrl + Shift + Enter to see the result.

Insert Excel TRANSPOSE Function to Convert Vertical Column to Horizontal

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


Method 3 – Enter the Formula as Text

STEPS:

  • Select B12 and enter EdB4.
  • Enter EdC4 in B13.

Type Formula as Text to Get Vertical Column into Horizontal

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.

Type Formula as Text to Get Vertical Column into Horizontal

  • Select B12:H13.

Type Formula as Text to Get Vertical Column into Horizontal

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

Type Formula as Text to Get Vertical Column into Horizontal

  • In the message box, click OK.

Type Formula as Text to Get Vertical Column into Horizontal

This is the output.

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


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

STEPS:

  • Select B12 and enter the formula below:
=INDEX($B$4:$C$10,COLUMN(A1),ROW(A1))

Swap Vertical Column to Horizontal Using INDEX Function in Excel

The first argument is B4:C10 (to convert to horizontal rows). COLUMN(A1) returns the column number of A11. 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.

Swap Vertical Column to Horizontal Using INDEX Function in Excel

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

Swap Vertical Column to Horizontal Using INDEX Function in Excel

This is the output.

Swap Vertical Column to Horizontal Using INDEX Function in Excel

  • This is the dataset after formatting:

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


Method 5 – Applying the OFFSET Function

STEPS:

  • Select B12 and enter the formula below:
=OFFSET($B$4,COLUMN(A1)-1,ROW(A1)-1)

Apply OFFSET Function to Switch Vertical Column

In the OFFSET function B4 is the reference. COLUMN(A1)-1 and ROW(A1)-1 denote the row and column numbers from the reference.

  • Press Enter and drag the Fill Handle down.

Apply OFFSET Function to Switch Vertical Column

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

Apply OFFSET Function to Switch Vertical Column

This is the output.

This is the dataset after formatting:

Read More: How to Move Data from Row to Column in Excel


Method 6 – Transpose a Vertical Column to a Horizontal row Using the INDIRECT Function

STEPS:

  • Use the formula below in C12:
=INDIRECT("B"&COLUMN()+1)

Transpose Vertical Column to Horizontal Using INDIRECT Function

The output of COLUMN() is 3. The formula becomes INDIRECT(B4). It returns Seller in C12.

  • Press Enter and use the formula below:
=INDIRECT("C"&COLUMN()+1)

  • Drag the Fill Handle to the right till Column I.

This is the output.

Read More: How to Paste Link and Transpose in Excel


Download Practice Workbook

Download the workbook.


Related Article


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

Get FREE Advanced Excel Exercises with Solutions!
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