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.
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
➤ Select the first group of cells that you want to merge (The books of Charles Dickens in this example).
➤ Go to the Home > Merge & Center tool in Excel toolbar under the section called Alignment.
➤ Click on the drop-down menu.
➤ From the options available, select Merge & Center.
➤ 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).
➤ You can repeat the same procedure for the rest group of cells to merge them into a single cell.
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
- Here I’ve used commas (,) in between the book names for looking presentable. You can use anything you like.
Next, repeat the same procedure for all groups of cells.
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)
- Here I’ve also used commas (,) in between the book names. You can use anything you like.
Next, you can use this formula for the rest group of cells to merge them into a single cell.
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.
➤Press ALT+F11 on your keyboard. The VBA window will open.
➤ Go to the Insert tab in the VBA window.
➤ From the options available, select Module.
➤ A new module window called “Module 1” will open.
➤ Insert the following VBA code in the module.
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
➤ Save the workbook as Excel Macro-Enabled Workbook.
➤ Return to your workbook and select the data set (Without the Column Headers).
➤ Press ALT+F8 on your keyboard.
➤ A dialogue box called Macro will open. Select Merging_Rows and click on Run.
➤ 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.
➤ 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.
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.