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

If You are looking for the easiest ways to convert multiple rows to a single row in Excel then You are in the right place. Sometimes it becomes essential to convert multiple rows into a single row but it becomes ineffective to do that manually.

So, here in this article, I will cover some easiest methods to do this tedious task within a few moments. Now, Let’s get into the article.

How to Convert Multiple Rows to Single row in Excel: 5 Ways

Here I have a dataset composed of 2 columns named as Employee ID and Employee Name and 5 rows. Using this data table I will show you how you can easily convert multiple rows into a single row.

Method-1: Using The TRANSPOSE Function

Step-01: Suppose I want to convert from Row 4 to Row 8 of Employee ID into a single row. So, here I used the TRANSPOSE function and then selected the whole range.
=TRANSPOSE(array)Here, array= B4:B8

Step-02: After entering the function the multiple Rows of the Employee ID Column will be transformed into a single Row.

Method-2: Using Power Query

Step-01: Suppose now I will convert all rows of Employee ID and Employee Name into a single row. It can be done easily using Power Query. For using Power Query at first the data table should be selected and then the From Table/Range option under the Data tab should be selected.

Step-02: After that, a Create Table dialog box would appear where My table has headers option should be selected and the whole dataset should be selected.

Step-03: Then Power Query Editor will appear and here follow these steps Transform tab >>Transpose command

Step-04: After following the above process the following table will be formed.

Method-3: Using The TEXTJOIN Function

Step-1: By using the TEXTJOIN function You can join all of the rows in a cell.
=TEXTJOIN( delimiter, TRUE, range)Here, the delimiter is a comma, TRUE is for Ignore empty cells, range=B5:B9

Step-02: After entering the function all of the row values has been combined in E7

Step-03: After that, the values combined in one cell have to be separated into different columns. For this, you have to follow the Data tab>>Data Tools>>Text to Columns

Step-04: Then Convert Text to Columns Wizard dialog box will open and it has 3 stages and here You have to select the Delimited option and click on Next to go to stage 2.

Step-05: In stage 2 You have to select Comma as Delimiters and then click on Next

Step-06: After that, in stage 3 you have to select the Column data format as General and then click on Finish.

Step-07: After completing the process, the following table will appear.

Method-4: Using The Ampersand Sign

Step-01: Using & many rows can be combined in a single cell.
=” 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.

Step-02: Then the rows will be combined in a cell named E6.

Step-03: Then following Step 3 to Step 6 of Method 3 all of the data in cell E5 will be separated into different columns as below.

Method-5: Using The CONCATENATE Function

Step-01: Using the CONCATENATE function all of the data of multiple rows can be combined as below. Also, the CONCAT function will do the same.
=CONCATENATE(Text1,Text2,Text3…)Here B4, B5, B6, and Space between them are used as texts.

Step-02: Then the rows will be combined in cell D4.

Step-03: Then following Step 3 to Step 6 of Method 3 all of the data in cell E5 will be separated into different columns as below.

Download Excel Workbook

Convert Multiple Rows to a Single Row.xlsx

Conclusion:

This article covered the possible easiest ways to convert multiple rows into a single row. I hope this will help you to do this task easily. If you have any further suggestions you can share them with us. Also, feel free to ask any questions.