How to Merge Cells Vertically Without Losing Data in Excel

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, you need to merge cells in Excel vertically without losing the data. It is one of the most important and widely used tasks. There are several functions in Excel that help you to merge the cells. The Merge & Center command also merges the cells but it takes only one value. Apart from that, we have demonstrated the other functions along with the VBA code to merge cells vertically without losing data in Excel in this article. So let’s get started.


How to Merge Cells Vertically Without Losing Data in Excel: 5 Methods

For doing the operation of merging cells vertically without losing data. Here, we have taken a dataset of some Book Records of Martin Bookstore. We will merge the vertical book lists of an Author.

Dataset

Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.


1. Using Ampersand Operator

To merge the values from a group of cells into one cell, you can use a formula comprising the Ampersand (&) symbol. This Ampersand operator joins the text accordingly. For using it go to cell D5 and insert the below formula.

=C5&", "&C6&", "&C7

Here the Ampersand (&) symbol adds the text of cells C5, C6, and C7 respectively.

Eventually, apply the same formula for other cells to get the following output.

Using Ampersand Operator to merge cells vertically without losing data


2. Applying CONCATENATE Function

Instead of using the Ampersand (&) symbol, you can use the CONCATENATE function of Excel to merge a group of cells into one cell. This function joins or merges multiple cells that you put as a form of argument.

In our case, we enter the formula in cell D5.

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

The above function joins the text of cells C5, C6, and C7. We also put commas as the argument to separate the books.

Consequently, apply the same formula for other cells to get the result like the image below.

Applying CONCATENATE Function to merge cells vertically without losing data


3. Employing CONCAT Function

The CONCAT function also merges the text of multiple cells as well. The CONCAT function also follows the same procedure as the CONCATENATE function. It does not provide any delimiter, whereas the CONCATENATE function does. Also, the CONCATENATE function is compatible with the earlier versions.

For employing the formula go to cell D5 and write up the formula.

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

The above function joins the text of cells C5, C6, and C7. We also put commas as the argument to separate the books.

Consequently, apply the same formula for other cells to get the result like the image below.

Employing CONCAT Function to merge cells vertically without losing data


4. Utilizing TEXTJOIN Function

The TEXTJOIN function concatenates texts with a delimiter. Also, it takes a command to ignore the empty cells. The function we have used is to merge cells vertically without losing data. 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 stands for ignoring empty. And finally, it joins C5, C6, and C7 cells.

Utilizing TEXTJOIN Function to merge cells vertically without losing data


5. Incorporating VBA Macros

All the methods mentioned above work 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 Macro code that will merge all the groups of cells into single cells in the easiest way possible. Follow the below steps to use the VBA code to merge cells in Excel.

Steps:

  • Firstly, select the entire dataset and hover over the Developer tab>> choose Visual Basic.

Incorporating VBA macros to merge cells vertically without losing data

Note: You can open the VBA window with the ALT + F11 key.

  • Apparently, a dialog wizard appears. Choose the Insert tab >> Module >> Module1.

  • Sequentially, write the 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
  • Moreover, run the above code with the F5 key.

VBA CODE to merge cells vertically without losing data

  • Consequently, a Macros window appears. Hit the Run from there for the Merging_Rows macro.

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


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

You can use the Merge & Center command from the Alignment section to merge the cells but it will keep only the upper left value. That means it loses the data which is a matter of concern. Though it loses data, we also discuss it here for your better visualization.

  • Select the first group of cells that you want to merge (The books of Charles Dickens in this example).
  • Eventually, go to the Home tab >> Merge & Center tool in the Excel toolbar under the section called Alignment.

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

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

Similarly, you can repeat the same procedure for the rest group of the cells to merge them into a single cell.


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


Conclusion

That’s all about today’s session. These are some easy methods to merge cells vertically without losing data in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
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

2 Comments
  1. Reply
    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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo