How to Convert Multiple Rows to Single Row in Excel (5 Methods)

A dataset composed of 2 columns named Employee ID and Employee Name and 5 rows will be used to show how to convert multiple rows into a single row.

Method 1 – Using The TRANSPOSE Function

Steps:

Use the TRANSPOSE function and select the desired range.
=TRANSPOSE(array)Here, array= B4:B8
Press ENTER.

Method 2 – Using Power Query

Steps:

Under the Data tab select the From Table/Range
In the Create Table dialog box scheck the My table has headers option and select the whole dataset.
Press ENTER. The Power Query Editor will appear
Select the desired range.
In the Transform tab select the Transpose command

Method 3 – Using The TEXTJOIN Function

Steps:

The TEXTJOIN function is defined as,
=TEXTJOIN( delimiter, TRUE, range)
Here, the delimiter is a comma, TRUE is for Ignore empty cells, range=B5:B9
Press ENTER. The row values are now combined in E7
Next, the combined values in one cell have to be separated into different columns. In the Data tab go to Data Tools>>Text to Columns.
In the first Convert Text to Columns Wizard dialog box select the Delimited option and click on
Select Comma as Delimiters and then click on
Select the Column data format as General and then click on
After completing the process, the following table will appear.

Method 4 – Using The Ampersand Sign

Many cells can be combined in a single cell with
=” Text 1”&” ”&”Text 2”&” ”&”Text 3”
In this way various texts can be combined with space as a separator, here B4, B5, B6 are used as texts.
Press ENTER.
Follow Step 3 to Step 6 of Method 3 to separate data into different columns as below.

Method 5 – Using The CONCATENATE Function

The CONCATENATE function is defined as,
=CONCATENATE(Text1,Text2,Text3…)
Here B4, B5, B6, and Space between them are used as texts.
Press ENTER.
Follow Step 3 to Step 6 of Method 3 to separate data into different columns as below.
<img class="alignnone size-full wp-image-37353" style="background-color: #ffffff; color: #333333; font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen-Sans, Ubuntu, Cantarell, 'Helvetica Neue', sans-serif;" src="https://www.exceldemy.com/wp-content/uploads/2021/10/convert-multiple-rows-to-single-row-20.png" alt="convert multiple rows to a single row" width="566" height="381" />