Tanjima Hossain
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
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.
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.
Similar Readings:
- Convert Multiple Rows to A Single Column in Excel (2 ways)
- How to Combine Multiple Rows into One Cell in Excel
- Combine Duplicate Rows and Sum the Values in Excel
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 to 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.
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.
