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

If You are looking for some 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.

Download Excel Workbook

Convert Multiple Rows to a Single Row.xlsx

5 Ways to Convert Multiple Rows to Single row in Excel

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.

dataset

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

Transpose function

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

Convert multiple rows to single row in Excel

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.

Creating Table of Power Query

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.

selecting Table range

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

Power Query Editor

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

convert multiple rows to a single row

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

Textjoin function

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

Output after using Textjoin function

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

Using Text to column

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.

Dialog box of text to column

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

convert text to column wizard

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

convert text to column wizard

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

convert multiple rows to a single row


Similar Readings:


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.

Using ampersand

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

combined texts

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

convert multiple rows to a single row

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.

Concatenate function

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

Concatenated texts

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.

convert multiple rows to a single row

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.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo