How to Merge Cells in Excel Vertically Without Losing Data

One of the most important and widely used tasks that we have to accomplish while working in Excel is to merge cells vertically without losing any data. Today I’ll show you how you can merge cells in Excel vertically without losing data.


Download Practice Workbook


4 Easy Methods to Merge Cells in Excel Vertically Without Losing Data

Here we’ve got a data set with the Names of some Authors and their Books in a bookshop called Martin Bookstore.

Data Set to Merge Cells Vertically Without Losing Data in Excel

Our objective today is to merge the book names of the same author vertically in one cell without losing any data.


1. Run Merge and Center Tool from Excel Toolbar to Merge Cells in Excel Vertically Without Losing Data

Step 1:

➤ Select the first group of cells that you want to merge (The books of Charles Dickens in this example).

Selecting Cells to Merge Cells Vertically Without Losing Data in Excel

Step 2:

➤ Go to the Home > Merge & Center tool in Excel toolbar under the section called Alignment.

Merge & Center Tool to Merge Cells Vertically Without Losing Data in Excel

Step 3:

➤ Click on the drop-down menu.

➤ From the options available, select Merge & Center.

Merge & Center Tool to Merge Cells Vertically Without Losing Data in Excel

Step 4:

➤ Click on Merge & Center.

➤ You will find your selected group of cells merged into one cell, but containing only the value from the first cell (Great Dickens in this example).

Merge Cells Vertically Without Losing Data in Excel

Step 5:

➤ You can repeat the same procedure for the rest group of cells to merge them into a single cell.

Merge Cells Vertically Without Losing Data in Excel

Read more: How to Merge and Center Cells in Excel


2. Use Ampersand (&) Symbol to Merge Cells in Excel Vertically Without Losing Data

The method mentioned above merges the group of cells into one single cell, but it doesn’t fulfill our requirement completely.

It keeps value from only the first cell, not from all the cells.

That means, it loses data.

To merge the values from a group of cells into one cell, you can use a formula comprising the Ampersand (&) symbol.

The formula for the first group of cells will be:

=C4&", "&C5&", "&C6

Note:

  • Here I’ve used commas (,) in between the book names for looking presentable. You can use anything you like.

Formula to Merge Cells Vertically Without Losing Data in Excel

Next, repeat the same procedure for all groups of cells.

Formula to Merge Cells Vertically Without Losing Data in Excel


3. Use Formula to Merge Cells in Excel Vertically Without Losing Data

Instead of using the Ampersand (&) symbol, you can use the CONCATENATE function of Excel to merge a group of cells into one cell.

The formula to merge the first group of cells will be:

=CONCATENATE(C4,", ",C5,", ",C6)

Note:

  • Here I’ve also used commas (,) in between the book names. You can use anything you like.

Formula to Merge Cells Vertically Without Losing Data in Excel

Next, you can use this formula for the rest group of cells to merge them into a single cell.

Formula to Merge Cells Vertically Without Losing Data in Excel


4. Run VBA Codes to Merge Cells in Excel Vertically Without Losing Data

All the methods mentioned above works perfectly fine, but still, they don’t fulfill our purpose completely.

We want to derive such a method that will merge all the groups of cells into single cells with a single click.

And yes, there are ways to do this. In this section, we will derive a method using a VBA code that will merge all the groups of cells into single cells in the easiest way possible.

Step 1:

Press ALT+F11 on your keyboard. The VBA window will open.

VBA Window to Merge Cells Vertically Without Losing Data in Excel

Step 2:

Go to the Insert tab in the VBA window. 

From the options available, select Module.

Inserting Module to Merge Cells Vertically Without Losing Data in Excel

Step 3:

A new module window called “Module 1” will open. 

Insert the following VBA code in the module.

Code:

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

VBA Code to Merge Cells Vertically Without Losing Data in Excel

Step 4:

Save the workbook as Excel Macro-Enabled Workbook.

Saving Macro to Merge Cells Vertically Without Losing Data in Excel

Step 5:

Return to your workbook and select the data set (Without the Column Headers).

Selecting Data Set to Merge Cells Vertically Without Losing Data in Excel

Step 6:

Press ALT+F8 on your keyboard.

A dialogue box called Macro will open. Select Merging_Rows and click on Run.

Running Macro to Merge Cells Vertically Without Losing Data in Excel

Step 6:

You will get a warning box that will you tell you merging cells only keeps the upper-left cell value, and discards the other values

Click on OK.

Warning Box to Merge Cells Vertically Without Losing Data in Excel

Step 7:

You will get the same warning box a few times. Click on OK every time.

Finally, you will find all your groups of cells merged vertically into single cells like this.

VBA Code to Merge Cells Vertically Without Losing Data in Excel


Conclusion

Using these methods, you can merge cells in Excel vertically without losing data. Do you know any other method? Or do you have any questions? Feel free to ask us.


Related Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo