# How to Merge Cells Vertically Without Losing Data in Excel

To demonstrate our methods of merging cells vertically without losing data, we’ll use the following dataset of some Book Records, and merge the books of an AuthorÂ vertically.

We used the Microsoft 365 version, but you may use any other version at your convenience. If any of the steps don’t work in your version, please leave a comment to let us know.

### Method 1 – Using Ampersand Operator

To merge the values from a group of cells into one cell, we can use a formula with the Ampersand (&) operator, which joins text.

Steps:

• In cell D5 enter the following formula:
`=C5&", "&C6&", "&C7`

The Ampersand (&) symbol joins the text of cells C5, C6, and C7 respectively, separated by commas.

• Use theÂ Fill Handle to apply the same formula to the other cells in column D.

### Method 2 – Using CONCATENATE Function

Alternatively, we can use the CONCATENATE function to merge a group of cells into one cell.

Steps:

• Enter the formula below in cell D5:
`=CONCATENATE(C5,", ",C6,", ",C7)`

The function joins the text of cells C5, C6, and C7Â using a delimiter of commas.

• Drag the Fill Handle down to apply the same formula to the other cells in column D.

### Method 3 – Using CONCAT Function

The CONCAT function also merges the text of multiple cells, but does not provide a delimiter. The CONCATENATE function is compatible with earlier versions of Excel, whereasÂ CONCAT is a new feature in Excel 365.

Steps:

In cell D5 enter the following formula:

`=CONCAT(C5,", ",C6,", ",C7)`

The function joins the text of cells C5, C6, and C7 with commas as a delimiter.

• Drag the Fill Handle down to apply the same formula to the other cells in column D.

### Method 4 – Using TEXTJOIN Function

The TEXTJOIN function concatenates texts with a delimiter, and allows empty cells to be ignored.

Steps:

• Enter the following formula in cell D5:
`=TEXTJOIN(",",TRUE,C5,C6,C7)`

The syntax uses the â€œ,â€œ as a delimiter to create commas between the texts. TRUE indicates ignoring empty cells. Cells C5, C6, and C7 are the cells to be joined.

• Drag the Fill Handle down to apply the same formula to the other cells in column D.

### Method 5 – Using VBA Macros

All the methods mentioned above work perfectly well, but they donâ€™t fulfill our purpose completely. We want to merge all the groups of cells into single cells with a single click. This can be accomplished using VBA Macro code.

Steps:

• Select the entire dataset.
• Click on the Developer tab.
• Choose Visual Basic.

Note: Alternatively, open the VBA window by pressing ALT + F11.

A dialog wizard appears.

• Click the Insert tab >> Module >> Module1.

• Enter the following code in Module 1:
``````Sub Merging_Rows()
Dim out As Variant
out = ""
Dim start As Variant
start = 1
Dim ending As Variant
ending = 1
Dim i As Variant
Dim j As Variant
For i = 2 To Selection.Rows.Count + 1
Â Â Â Â If Selection(i, 1) <> "" Or i = Selection.Rows.Count + 1 Then
Â Â Â Â Â Â Â Â ending = i - 1
Â Â Â Â Â Â Â Â For j = start To ending
Â Â Â Â Â Â Â Â Â Â Â Â If j = ending Then
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â out = out + Range(Selection(j, 2).Address).Value
Â Â Â Â Â Â Â Â Â Â Â Â Else:
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â out = out + Range(Selection(j, 2).Address).Value + vbNewLine
Â Â Â Â Â Â Â Â Â Â Â Â End If
Â Â Â Â Â Â Â Â Next j
Â Â Â Â Â Â Â Â Range(Selection(start, 2).Address) = out
Â Â Â Â Â Â Â Â Range(Selection(start, 1).Address + ":" + Selection(ending, 1).Address).Merge Across:=False
Â Â Â Â Â Â Â Â Range(Selection(start, 2).Address + ":" + Selection(ending, 2).Address).Merge Across:=False
Â Â Â Â Â Â Â Â start = i
Â Â Â Â Â Â Â Â out = ""
Â Â Â Â End If
Next i
End Sub``````
• Run the code by pressing the F5 key.

A Macros window appears.

• Select the Merging_Rows macro.
• ClickÂ Run.

All our groups of cells are merged vertically into single cells like this:

## How to Merge Multiple Cells Vertically but Keep Only the Upper-Left Value

We can use the Merge & Center command from the Alignment section to merge the cells, but it will keep only the upper left value. Although this method is not ideal because it loses the other data, we discuss it here for your understanding.

Steps:

• Select the first group of cells to be merged (The books of Charles Dickens in this example).
• Go to the Home tab.
• Select the Merge & Center tool under Alignment.

The selected group of cells is merged into one cell containing only the upper left value from the first cell (Great Expectations in this example).

• Repeat the procedure for the other groups of cells.

<< Go Back To Excel Concatenate Multiple Cells | Concatenate Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

Slayton McDonough Nov 21, 2023 at 11:18 PM

What if the ‘data’ is merely text? Why should only the upper left value be preserved? This is yet another example of Excel at its worst.

• Dear Slayton,

Thank you for raising a valid concern about preserving text data in Excel. I completely agree that the current limitation, preserving only the upper-left value during a merge, can be frustrating.

To address this, I suggest exploring the five methods mentioned earlier to find the most suitable workaround for your specific case. Additionally, let’s hope that Microsoft Excel considers implementing a new merge option in the future, one that preserves all cell data.

Your feedback is valuable, and we appreciate your engagement.

Best regards,
Aniruddah
Team Exceldemy

Advanced Excel Exercises with Solutions PDF