When you are dealing with bigger data files in Excel, you might be having many rows and columns in a sheet. Sorting out each entry present in the sheet could be a daunting task. What if you were able to convert all those lengthy rows or columns into single row or column, does it excite you?
Most of the time, people get stuck in the execution of the multiple functions in Excel. That’s why different tutorials and easy-to-follow tricks are available on the web. In this case, when you need to convert more than one row into single row, you also must be confused and thinking about how to convert multiple rows to single row in Excel, right?
Converting multiple rows to single row manually could be a tiresome task. That’s why we have come up with a number of convenient methods for you to apply while working with bigger data sets. So, here in this post, you will find out the best tricks. Let’s get started!
How to Convert Multiple Rows to Single Row in Excel
Below you will find a dataset containing 5 rows and 2 columns; Employee ID and Employee Name. we will use this data set to understand how to convert multiple rows to single row in Excel.
Convert Multiple Rows to Single Row using the Transpose Function
- Assume that we need to convert from row 4 to row 8 named as Employee ID to a single row. Using the TRANSPOSE function, we will select the entire range.
=TRANSPOSE (array)
In this case, array = B4:B8
- Once you put the function, you will see that multiple rows of the Employee ID column are converted to a single row.
Isn’t that easy?
How to Convert Multiple Rows to Single Row using Power Query
- Using the same dataset as above, now assume that we need to convert all rows of Employee ID and Employee Name to a single row. With the help of Power Query, you can do this task easily. For this, you have to choose the data table first, and then choose the From Table/Range option given under the Data tab.
- Later on, you will see a dialogue box named Create Table, from which you have to choose My table has headers option and it will select the entire dataset.
- Next, you will see the Power Query Editor and select the Transform tab then the Transpose option.
- Once the above step is completed, you will see the following table format.
How to Convert Multiple Rows to Single Row using the TEXTJoin Function
Let’s understand another method used for converting multiple rows to a single row.
- The TEXTJOIN function lets you combine all the rows in a cell.
=TEXTJOIN (delimiter, TRUE, range)
In this function, the delimiter is a comma, TRUE is used for Ignore empty cells, and range = B5:B9
- Once the function is entered, you will see all the row values joined in E7.
- Later on, you will have to separate all the values into different columns. For this, follow this function sequence: Data tab > Data Tools > Text to Columns
- Now, a dialogue box named Convert Text to Columns Wizard will appear with 3 phases. From this, you ought to choose the Delimiter option and hit on the Next option to go on next phase.
- You have to choose Comma as Delimiters in phase 2, and then click on the Next option.
- Entering phase 3 needs you to choose the Column data format as General and then hit on the Finish option.
- Once the above steps are completed successfully, you will see the following table format.
How to Convert Multiple Rows to Single Row using the Ampersand Sign
With the Ampersand sign “&” you can easily combine multiple rows into a single cell.
=” Text 1 “&” “&” Text 2 “&” “&” Text 3”
Following this trick, you can join multiple texts with space as a separator. Here, in this case, B4, B5, and B6 are for the texts.
In cell E6, you will see the rows are combined.
Now, you have to follow the steps from 3 to 6 of the TEXTJOIN function method and you will see the data from cell E5 is shifted to different columns.
So, this is another great approach to follow that helps you understand how to combine multiple rows into one row in Excel.
How to Convert Multiple Rows to Single Row using the CONCATENATE Function
With CONCATENATE function, you can simply combine the entire rows just like below.
=CONCATENATE (Text1, Text2, Text3…)
In this case, B4, B5, B6, and the space between them are considered texts.
Now, in cell D4 you will see all the rows are combined.
Now, from the method TEXTJOIN function, you have to follow the steps from 3 to 6 and all the data will be separated into different columns from cell E5.
Excel combines multiple rows into one smoothly with this function application. All you need to do is follow the steps carefully.
Convert Multiple Rows to Single Row with VBA Macro
VBA always appears whenever you need to do something uncommon. With VBA Macro, you can even convert multiple rows to a single row or column as per your choice. Below are some easy steps to follow:
Open the Excel workbook and hit on the “Visual Basic” option given under the DEVELOPER tab. Or else, you can just press the “ALT + F11” shortcut right from your keyboard.
Now, you will see the “Visual Basic Editor” window.
Choose “Insert” > “Module” to make a new module.
Simply paste the VBA code into the given code window and hit the “Save” button.
Sub convertMultipleRowsToOneRow()
Set myRange = Application.InputBox(“select one range that you want to convert:”, “”, Type:=8)
Set dRang = Application.InputBox(“Select one Cell to place data:”, “”, Type:=8)
rowNum = myRange.Rows.Count
colNum = myRange.Columns.Count
For i = 1 To rowNum
myRange.Rows(i).Copy dRang
Set dRang = dRang.Offset(0, colNum + 0)
Next
End Sub
Go to the current worksheet and run the above-mentioned Excel macro. Click on the Run button.
Choose a range that is supposed to convert and hit the OK button.
Simply choose a cell to put data and hit OK.
That’s it.
This is how you can combine Excel rows into one row easily with VBA macro coding.
Convert Multiple Rows to Single Row using Formula
Suppose that your data set includes range A1:B6, and that data is needed to be converted into a single row. So, how will you do it?
A formula based on the OFFSET function, the ROW function, the FLOOR function, and the COLUMN function is used to do it.
=OFFSET(Sheet8!$A$1,((ROW()-1)*6)+(FLOOR(COLUMN()-1,2)/2),(COLUMN()-1)-(FLOOR(COLUMN()-1,2)))
For this, you have to type this formula in another worksheet but the existing workbook. Now, drag the Autofill handle to the right side unless and until you get the number 0.
Also, note that the total number of rows is 6 and the total number of columns is 2.
To Sum Up
Before coming to this post, you must be thinking about how do I combine multiple rows into one row. Isn’t it?
Now, you can easily turn the tables and get the ball in your court. This is how Excel combines two rows into one row instantly.
To conclude, we understand that Excel has loads of built-in functions to perform multiple computational tasks. However, sometimes you have to confront an uncommon query and for this, you need to think differently. Above you can see different ways to convert multiple rows to a single row. And hopefully, now you can easily manage to perform this function with your dataset in Excel. Best of luck!
