6 Suitable Ways to Convert Multiple Rows to Single Row in Excel

Converting multiple rows to single row might be easy when you are only copying them manually, and this is also for a small amount of data. If you want to work with a huge data-set you might seek some other ways. We are presenting you some of the methods which will help you to Convert Multiple Rows to Single Row in Excel.

Contents hide

📁Download Excel File

Learn to Convert Multiple Rows to Single Row in Excel with These 6 Suitable Approaches

1. Using TRANSPOSE Function

2. Utilizing TEXTJOIN Function

3. Applying Ampersand Sign

4. Using CONCATENATE Function

5. Employing VBA Code

6. Implementing Power Query

📝 Takeaway from This Article

Conclusion

📁Download Excel File

Download the Excel file we used to create this article so you can practice.

Convert Multiple Rows to a Single Row.xlsx

Learn to Convert Multiple Rows to Single Row in Excel with These 6 Suitable Approaches

Here are six possible approaches which you can use to Convert Multiple Rows to a Single Row in Excel. Here, we have used a data set consisting of the student’s name and their ID no and applied our methods to this. This is a data set of two rows and two columns.

Approach

1. Using TRANSPOSE Function

The TRANSPOSE function can be used to transpose a dataset. For example, I would like to convert the rows of student Id into single row.

To use this function while converting several rows to a single row, follow the steps given below.

⬇️⬇️ STEPS ⬇️⬇️

Select a cell where you want to see your data in single row. Here we have selected cell E6.
Then type the function with TRANSPOSE, and then insert a bracket, within the bracket type/select the range of data.

Or, you can simply copy this formula from below.

=TRANSPOSE(B6:B11)

Here, B6:B11 is the range of rows that we want to convert into single row.

Enter the formula and you can see that the rows you selected are now arranged in single row.

Approach

2. Utilizing TEXTJOIN Function

The TEXTJOIN function can join texts from multiple ranges. Let’s see how you can apply this for this purpose.

I am going to apply this to convert the multiple rows of student id into one single row.

⬇️⬇️ STEPS ⬇️⬇️

First, select the cell of your desired single row as I selected cell E7, and then type the function TEXTJOIN.
The structure of a TEXT JOIN function is, TEXTJOIN(delimiter, TRUE, range). The delimiter will be a comma, to ignore blank cells TRUE will be used and range will be the range you want to convert.
So, the function for our dataset will be,

=TEXTJOIN(“,”,TRUE,B6:B11)

The row range for my dataset is B6:B11.

As we have mentioned before this function allows you to convert multiple rows into a single row. After pressing the Enter key from the keyboard you must be able to see all texts join together in the cell E6.

To detach all the values in the different columns, first, copy the formula and then go to the Paste special option.

A dialogue box will appear and from there select only the box value, and then press OK and you can only see the values now.

Now, go to Data tab, from the Data Tools group click on Text to Columns.

A new window will appear, known as Convert Text to Columns Wizard, you will see Delimited option on step 1 . Select that and click Next.

In the step 2 window, among other tick boxes tick the box of Comma and then select Next to navigate to the next step window.

Now from this 3rd step window select the option General and finally select Finish option.

You will be able to see the given format of your dataset.

Approach

3. Applying Ampersand Sign

You may know the ampersand (&) sign of your keyboard. But did you know that this can be used to convert multiple rows into single row? Let’s find out how to do this.

⬇️⬇️ STEPS ⬇️⬇️

First, go to the cell you want to create your single row, in this case, this is the cell E6 and I am going to convert the rows that range from B6 to
Now, write your function following this format, =” Text 1”&” ”&”Text 2”&” ”&”Text 3”……

Here, Text 1, and Text 2 will be the rows that you want to convert. So, for my dataset, I have written the function below.

=B6&” “&B7&” “&B8&” “&B9&” “&B10&” “&B11&” “

Don’t forget to put a space (” “) which works as separator.

Now press Enter key and you will see the values. But these all are in one cell. We will show further steps on how to assign these values in different columns.

To separate all the values in the different columns, first, copy the formula and then go to the Paste Special.
A dialogue box will appear and from there select only the box values, and then press OK and you can only see the values now.

Now, go to the Data tab, from the Data Tools group click on Text to Columns.

A new window will appear, named Convert Text to Columns Wizard, you will see the Delimited option on step 1 . Select that and click Next.

In the step 2 window, among other tick boxes tick the box of Comma and then select Next to navigate to the next step window.

Now from this 3rd step window select the option General and finally click on Finish.

You will be able to see the following format of your dataset.

Approach

4. Using CONCATENATE Function

The CONCATENATE function is used to convert one or more strings into one string. Here, it can be used to convert multiple rows into a single row.

Just follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

First, go to the cell you want to create your single row, in this case, this is the cell E6 and I am going to convert the rows that range from B6 to
Now, type a formula utilizing the CONCATENATE The format of this function is =CONCATENATE (Text1, Text2, Text3…). In Text1, Text2, Text3…we will be writing the cell numbers of the rows that we want to convert.
So we entered the following formula.

=CONCATENATE(B6,” “,B7,” “,B8,” “,B9,” “,B10,” “,B11)

Here the gap between the cell numbers is also considered as text and we have used these to insert space between values.

Now Enter the formula, and you will get a string of data.

To separate all the values in the different columns, first, copy the formula and then go to the Paste Special.
A dialogue box will appear and from there select only the box value, and then press OK and you can only see the values now.

Now, go to the Data tab, from the Data Tools group click on Text to Columns.

A new window will appear, named Convert Text to Columns Wizard, you will see the Delimited option on step 1 . Select that and click Next.

In the step 2 window, among other tick boxes tick the box of Comma and then select Next to navigate to the next step window.

Now from this 3rd step window select the option General and finally click on Finish.

You will be able to see the following format of your dataset.

Approach

5. Employing VBA Code

Are you familiar with the VBA codes in Excel? Don’t worry if your answer is no. Just jump into the following steps.

⬇️⬇️ STEPS ⬇️⬇️

First, click the Developer tab option from the ribbon. And from there, click Visual Basic option to open. But, remember that, the Developer option may not be available in your ribbon, in this case, you have to go to the option and, from there, select the developer from view.

After opening the Microsoft Visual Basic for Applications, click the Insert and click Module.

Insert the following code in the module opened.

Sub Convert_into_One_Row()
Dim InRng As Range, OutputRng As Range
xTitleId = "ExcelDen"
Set InRng = Application.Selection
Set InRng = Application.InputBox("Ranges to be transform :", xTitleId, InRng.Address, Type:=8)
Set OutputRng = Application.InputBox("Paste to (single cell):", xTitleId, Type:=8)
Application.ScreenUpdating = False
xRows = InRng.Rows.Count
xCols = InRng.Columns.Count
For i = 1 To xRows
InRng.Rows(i).Copy OutputRng
Set OutputRng = OutputRng.Offset(0, xCols + 0)
Next
Application.ScreenUpdating = True
End Sub

Now from the Developer’s code section, select the Macros.

A dialogue box will open; select the option Run to run the code.

After running the code you will see a dialogue box, in the Ranges to be transform box you have to enter the cell preferences that you will convert. So, I choose the B6 to B11 cells, entering $B$6:$B$11. Then press the OK and another dialogue box will appear.

This time you have to choose the cell preference where you want to see the converted data, I chose the cell E6, so I entered $E$6. Finally, press OK.

You will see the converted row in your preferred cell location.

Approach

6. Implementing Power Query

Now, I want to convert both columns and rows of the dataset into one row. You can use the Microsoft Power Query for this purpose.

To learn how to solve this, go through the steps below.

⬇️⬇️ STEPS ⬇️⬇️

Firstly, select the whole dataset and then go to the Data tab from there click the From Table/Range.

A dialogue box named create Table will appear to click OK there but make sure to click on the My table has headers.

Therefore, the Power Query Editor will show up with the table.

From the ribbon, you will find the Transform option from there select the Transpose.

Notice that the table has been transposed.

But your work is not over yet, go to the Home option and select the Close & load option from there.

The Power Query Editor will be closed and you will see that the table has been created in a new worksheet.

📝 Takeaway from This Article

📌 We have learned how to convert multiple rows into single row using some Excel functions and VBA code.

📌 Using the Power Query Editor, you can convert rows of both columns at the same time.

📌 Write the VBA code carefully or an error may result.

Conclusion

While handling an ample amount of data it becomes a tiresome task to convert multiple rows into a single row. Excel features can rescue you and save you valuable time from wasting. Please leave a comment if you have any suggestions or questions. Don’t forget to visit our Excelden page to enhance your Excel-related knowledge.

(Visited 113 times, 1 visits today)