Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Combine Multiple Rows into One Cell in Excel (6 Ways)

Excel is a popular and useful tool for analyzing large sets of data. Sometimes we need to show the multiple rows of data in one cell to mean something or to create a new column. In this article, we will describe six different but easy methods to combine multiple rows in one cell in Excel. So, let’s start this article and explore these methods.


Download Practice Workbook


6 Simple Methods to Combine Multiple Rows in One Cell in Excel

In this section of the article, we will learn six simple methods to combine multiple rows in one cell in Excel. Let’s say, we have the List of Favorite Fruits of Employees as our dataset. In our dataset, we have two columns named Name and Favorite Fruits. Our goal is to combine multiple rows in one cell.

multiple rows in one cell excel

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.


1. Using Ampersand Operator

Using the Ampersand Operator is one of the easiest ways to combine multiple rows in one cell in Excel. The Ampersand operator allows us to join multiple text strings together. Now, let’s follow the steps mentioned below to do this.

Steps:

  • Firstly, use the following formula in cell D5.
=C5&" "&C6&" "&C7&" "&C8

Here, cells C5, C6, C7, and C8 indicate the first four cells of the Favorite Fruits columns.

  • Following that, press ENTER.

Using Ampersand Operator to combine multiple rows in one cell in Excel

Consequently, you will have the first four cells of the Favorite Fruits column combined in cell D5 as shown in the following image. Here, each cell is separated by a Space.

  • After that, use Excel’s AutoFill feature to obtain the remaining outputs.

Using AutoFill option to combine multiple rows in one cell in Excel

If you want to separate your row’s content using comma (,), space, or any character, insert those marks in between the space of the double quote (“ ”).

  • To do this, type the formula given below in cell D5.
=C5&","&C6&","&C7&","&C8
  • Then, press ENTER.

As a result, you will get the combined list of the first four cells of the Favorite Fruits column. Here, each cell is separated by a comma.

  • Finally, use Excel’s AutoFill option to get the combined list for the rest of the cells as demonstrated in the image below.

Final output of method 1 to combine multiple rows in one cell in Excel

Read more: How to Combine Rows into One Cell in Excel


2. Applying CONCAT Function

Applying the CONCAT function is another effective way to combine multiple rows in one cell in Excel. Let’s use the instructions outlined in the following section to do this.

Steps:

  • Firstly, apply the following formula in cell D5.
=CONCAT(C5,", ",C6,", ",C7," and ",C8)
  • Following that, hit ENTER.

Applying CONCAT Function to combine multiple rows in one cell in Excel

Subsequently, you will have the following output as shown in the image below.

  • Lastly, apply Excel’s AutoFill feature to get the combined list of Favorite Fruits for the remaining cells.

Final output of method 2 to combine multiple rows in one cell in Excel


3. Utilizing CONCATENATE and TRANSPOSE Functions

Now, we will use the TRANSPOSE function within the CONCATENATE function. The TRANSPOSE function will change the layout of the data and CONCATENATE will combine the data. So, let’s use the steps mentioned below to combine multiple rows in one cell in Excel.

Steps:

  • First, select the cell where you want to put your combined multiple row data.
  • Then, use the TRANSPOSE function first. The Formula is given below.
=TRANSPOSE(C5:C8)

Here, the range of cells C5:C8 represents the first four cells of the Favorite Fruits column.

  • Following that, press the F9 key.

Utilizing CONCATENATE and TRANSPOSE Functions to combine multiple rows in one cell in Excel

After pressing the F9 key, you will see the row values within curly braces as marked in the following picture.

  • Now, remove the curly braces and use the CONCATENATE function. It will combine all selected rows without space. We will use the following formula here.
=CONCATENATE("Grape","Apple","Orange","Cherry")
  • Subsequently, press ENTER.

Using CONCATENATE function to combine multiple rows in one cell in Excel

As a result, you will have the following output on your worksheet.

  • To make multiple rows values clear, use comma (,) character (and ) as a separator within double quote (“ ”).
=CONCATENATE("Grape",",","Apple",",","Orange"," and ","Cherry")
  • Now, hit ENTER.

Consequently, a comma will be added between the texts and the “and” separator will be added before the last text as demonstrated in the following image.

  • Finally, use Excel’s AutoFill feature to get the rest of the outputs.

Final output of method 3 to combine multiple rows in one cell in Excel


4. Implementing TEXTJOIN Function

Implementing the TEXTJOIN function is one of the most efficient ways to combine multiple rows in one cell in Excel. So, let’s use the procedure discussed in the following section to do this.

Steps:

  • Firstly, use the following formula in cell D5.
=TEXTJOIN(",",TRUE,B5:B8)

Here, the range of cells B5:B8 refers to the first four cells of the Name column.

  • After that, press ENTER.

Implementing TEXTJOIN Function to combine multiple rows in one cell in Excel

As a result, you will have the combined list of the first four cells of the Favorite Fruits column in cell D5. Here, we used a comma (,) as a delimiter.

  • Lastly, you can use Excel’s AutoFill option to obtain the remaining outputs as shown in the image below.

Final output of method 4 to combine multiple rows in one cell in Excel


5. Employing Formula Bar

In this section of the article, we will learn to employ the Formula Bar to combine multiple rows in one cell in Excel. Now, let’s use the procedure discussed in the following section to do this.

Steps:

  • Firstly, select the cells you want to combine and press CTRL + C to copy the cells.
  • After that, open the Notepad application from your computer.
  • Now, press the keyboard shortcut CTRL + V to paste it into the Notepad.
  • Then, again select the Names and press CTRL + C to copy the Names.

Employing Formula Bar to combine multiple rows in one cell in Excel

  • Now, select the cell where you want to show the combined list of Names. In this case, we selected cell D5.
  • Following that, go to the Formula Bar and press CTRL + V to paste the copied Names from the Notepad.

Consequently, you will have the list of Names combined in cell D5.

  • Now, follow the same steps to get the following outputs as shown in the following image.

Final output of method 5 to combine multiple rows in one cell in Excel


6. Incorporating VBA Macro

Incorporating the VBA Macro is another smart way to combine multiple rows in one cell in Excel. Let’s follow the steps mentioned below to do this.

Steps:

  • Firstly, go to the Developer tab from Ribbon.
  • Following that, select the Visual Basic option from the Code group.

Incorporating VBA Macro to combine multiple rows in one cell in Excel

As a result, the Microsoft Visual Basic for Applications window will appear on your worksheet.

  • Now, in the Microsoft Visual Basic for Applications window, go to the Insert tab.
  • Then, choose the Module option from the drop-down.

Inserting Module to combine multiple rows in one cell in Excel

  • After that, write the following code in the newly created Module1.
Sub Multiple_Rows_into_One_Cell()
Dim i As Integer
If Selection.Cells.Count > 1 Then
For i = 1 To Selection.Cells.Count
Cells(ActiveCell.Row, 4).Value = Cells(ActiveCell.Row, 4).Value _
& " " & Selection.Cells(i).Value
Next i
End If
End Sub

Writing the VBA code to combine multiple rows in one cell in Excel

Code Breakdown

  • Firstly, we initiated a sub procedure named Multiple_Rows_into_One_Cell.
  • After that, we declared a variable i as Integer.
  • Then, we used the IF statement to check the count of selected is greater than 1 or not.
  • Following that, we used a For Next loop to assign the combined list of the selected cells in cell D5.
  • Here, we used Space as a separator.
  • Then, we closed the For Next loop.
  • Subsequently, we ended the IF
  • Finally, we terminated the sub procedure.
  • After writing the code, click on the Save option.

  • Then, press the keyboard shortcut ALT + F11 to return back to the worksheet.
  • Following that, select the cells you want to combine. In this case, we selected cells C5:C8.
  • Next, use the keyboard shortcut ALT + F8 to open the Macro Window.

Consequently, the Macro dialogue box will open on your worksheet as shown in the following image.

  • Now, in the Macro dialogue box, select the Multiple_Rows_into_One_Cell option.
  • Then, click on the Run option.

As a result, you will have the combined list of your selected cells in cell D5.

  • Similarly, follow the same steps for the rest of the cells and you will have the following output as demonstrated in the following picture.

Final output of method 6 to combine multiple rows in one cell in Excel


How to Insert New Line Within a Cell in Excel

In this section of the article, we will learn how we can insert a new line within a cell in Excel. To insert a new line, let’s follow the instructions outlined below.

Steps:

  • Firstly, type in the first cell of the Favorite Fruits column as shown in the image below.
  • Then, use the keyboard shortcut ALT + ENTER to insert a new line.

How to Insert New Line Within a Cell in Excel

  • Now, type in the second cell of the Favorite Fruits column and then press ALT + ENTER.

  • Now, repeat these steps and you will get the following outputs.

Final output of method 7 to Insert New Line Within a Cell in Excel

You can also use any of the methods described in this article to insert a new line within a cell in Excel.


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

practice section to combine multiple rows in one cell in Excel


Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to combine multiple rows in one cell in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo