How to Merge Vertical Cells in Excel (6 Easy Ways)

The Merging operation helps us combine more than one cell in Excel. Often, we find a data sheet that contains a lot of columns and rows, and this is very inconvenient to handle. However, if we use merging, the data sheet gets a simpler look and becomes easier for us to handle. In this article, we will show you some ways to merge vertical cells in Excel.

Download Practice Workbook

You may download the following workbook to practice yourself.

6 Suitable Methods to Merge Vertical Cells in Excel

In order to illustrate, we will use a sample dataset. For instance, the following dataset represents the brands and models of motorcycles available in a shop. We want to apply different ways of merging it depending on different conditions.

Method 1: Applying Merge & Center Command to Merge Vertical Cells with Keeping Only Upper-Left Value

Merge and Center is a great option in Excel to merge more than one cell. However, in the case of vertical merging by this feature, we lose some of the data.

Steps:

• At first, we select the data that we want to merge and apply the Merge & Center command.

• Secondly, by hitting ENTER, we get the result of merging.
• Merging in vertical cells keeps only the uppermost value. We will lose all other values.

Read More: How to Merge Cells Vertically Without Losing Data in Excel

Method 2: Utilizing Format Cells Box for Merging Cells Vertically

Another way to do the same thing as Merge & Center command is to utilize Format Cells. We will follow the steps below to do it.

Steps:

• We select the cells as before.

• Then, we press Ctrl+1 together, and the Format Cells box appears. In the box, we select Alignment, Merge Cells, and OK one after another and we get the result.

• We got the result of Merging only the Suzuki Brand with having only the upper-left value.

Read More: How to Merge and Center Selected Cells in Excel (4 Ways)

Method 3: Using Ampersand Operator to Merge Vertical Cells Without Losing Data

It is also possible to use operators to merge cells in Excel. In this method, we will use the Ampersand operator to merge cells vertically with all values without losing any data.

Steps:

• First, we apply the formula at D5.

• Then, we will hit ENTER and get the merged cell.

• Finally, we will use the AutoFill feature to fill the remaining cells.

Read More: How to Merge Text from Two or More Cells into One Cell in Excel

Similar Readings

• How to Unmerge Cells in Excel (7 Easy Methods)
• Merge Text from Two Cells in Excel (7 Methods)
• How to Merge and Center Cells in Excel (4 Easy Methods)
• Merge Multiple Cells in Excel at Once (3 Quick Ways)
• How to Merge Two Cells in Excel Without Losing Data (2 Ways)

Method 4: Applying CONCATENATE Function for Merging Cells Vertically

CONCATENATE is a function that we can use in merging cells. To do that, we will follow the following steps.

Steps:

• First, we apply the formula at D5.

• Then, we will hit ENTER to get the first value as well.

• Finally, we will use the AutoFill feature to fill the remaining cells.

Read More: How to Merge Cells in Excel with Data (7 Quick Ways)

Method 5: Using TEXTJOIN Function to Merge Vertical Cells in Excel

We will get the same value using the TEXTJOIN function instead of the CONCATENATE function. The steps are as follows.

Steps:

• First, we apply the formula at D5.

• Then, we hit ENTER to get the first value as well.

• Finally, we will apply the AutoFill feature to fill the remaining cells.

Read More: How to Merge Text Cells in Excel (9 Simple Methods)

Method 6: Implying VBA Code to Merge Vertical Cells in Excel

The last and most effective method to perform merging is to use VBA code. It will automate all the steps and make it easier for us. We will follow these steps.

Steps:

• Initially, we will select Developer and then Visual Basic.

• Next, click on Insert > Module.

• After that, we will write the following code.

Sub Merge_Vertical() 
Dim out As Variant
Application.DisplayAlerts = False
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 
			

• Finally, run it by pressing F5.
• Therefore, the result will look like the image below.

Read More: VBA to Merge Cells in Excel (9 Methods)

Conclusion

In the above sections, we have discussed various methods on how to merge vertical cells in Excel. Hope this will be very helpful for you. Let us know your views and recommendations in the comment section. Have a good day.

Related Articles

• How to Merge Multiple Cells in Excel (5 Suitable Ways)
• Merge Datasets in Excel (5 Easy Methods)
• How to Merge Multiple Cells without Losing Data in Excel (6 Methods)
• [Fix:] Excel Unable to Merge Cells in Table
• How to Merge Data from Multiple Workbooks in Excel (5 Methods)
• Merge Data in Excel from Multiple Worksheets (3 Methods)