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:

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:

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:

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:

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.

Download Practice Workbook

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