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.

## How to Combine Multiple Rows in One Cell in Excel: 6 Simple Methods

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

**. Our goal is to combine multiple rows in one cell.**

*Favorite Fruits*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**.

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.

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.

### 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**.

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

- Lastly, apply Excel’s
**AutoFill**feature to get the combined list offor the remaining cells.*Favorite Fruits*

**Read More:** How to Merge Two Rows 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.

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**.

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.

### 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**.

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.

### 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*.

- 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.

### 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.

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.

- 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
```

**Code Breakdown**

- Firstly, we initiated a sub-procedure named
**Multiple_Rows_into_One_Cell**. - After that, we declared a variable
**i**as an**Integer**. - Then, we used the
**IF**statement to check whether the count 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 subprocedure.

- 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.

## 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
column as shown in the image below.*Favorite Fruits* - Then, use the keyboard shortcut
**ALT+ENTER**to insert a new line.

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

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

## Practice Section

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

**Download Practice Workbook**

## 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.

## Further Readings

- How to Combine Rows with Same ID in Excel
- How to Merge Rows in Excel Based on Criteria
- Excel Merge Rows with Same Value
- How to Merge Rows Without Losing Data in Excel
- How to Merge Rows with Comma in Excel

- How to Merge Rows and Columns in Excel
- How to Convert Multiple Rows to a Single Column in Excel
- How to Convert Multiple Rows to Single Row in Excel

**<< Go Back to Merge Rows in Excel | Merge in Excel | Learn Excel**