How to Merge and Combine Cells in Excel – Explained

Merging cells in a spreadsheet means taking two or more cells and constructing a single cell out of them. Merging is generally used as a cosmetic trick to center a title over a particular section in a spreadsheet.

Below image clearly shows how a merged cell looks like.

How to Merge Cells in Excel

In this post we will see different ways to merge cells in Excel. But before that let’s understand what makes merging different from concatenation.

How Merging is different from Concatenation?

Many times people confuse between these two words. As I have foretold, Merging is the process of taking two or more cells and constructing a single cell out of them. On the other hand, Concatenation is the process of joining or combining the values from two or more cells.

Recommended Reading: Concatenate Cells in Excel

There are different methods to merge cells in Excel. In the below section we will understand how to do this:

Method 1: Merging multiple cells using the Merge and Centre button in Ribbon:

In this method we will see how to merge cells in excel using the Merge and Centre Button in Excel. Follow the below steps to do this:

  • First of all select the cells that you wish to merge.

Cells-to-be-merged-are-selected

  • After this navigate to the “Home” tab in the top ribbon and click the “Merge & Centre” button as shown in the below image.

Merge-and-centre-button-clicked

  • This will merge all the selected cells and the output will look like the below image.

Selected-Cells-Have-Been-Merged

Method 2: Merging multiple cells using the Format Cells option:

In this method we are going to utilize the Format Cells option to merge the selected cells.

  • First of all similar to the previous method, select the cells that you have to merge.
  • Next, click the “More Options” button near the “Alignment Settings” on “Home” Tab as shown.

Alignment-Settings-More-Options-button

Alternatively you can also Right Click the selected range and click the “Format Settings” option from the Right Click menu.

Format-Cells-option-in right-click-menu

Tip: You can also use the shortcut Alt + H F A for performing this.

  • After this “Format Cells” window will open. Now, in the “Format Cells” window navigate to “Alignment” tab and check the “Merge” checkbox and click “Ok”.

Format-Settings-Merge-option

  • This will merge the selected cells.

Method 3: How to Merge cells in Excel using VBA Macro:

If you wish to merge cells using a VBA program then you should try the below codes:

A. VBA Code to merge Selected sells:

Sub Merge_Cells()
If Selection.Cells.Count > 1 Then
  Selection.Merge
  MsgBox "Selected cells Merged!"
Else
  MsgBox "For Merging please select At-Least 2 or More cells!", vbExclamation
End If
End Sub

This code merges the cells that are selected by the user. The code will only execute if the selected cells are greater than 1.

Recommended Reading: IF Statement, Message Box in VBA

A. VBA Code to merge Specified cells:

Sub Merge_Specified_Cells()
Dim Cells As String
On Error GoTo ErrorHandler
Cells = InputBox("Enter the range of cells that you wish to merge." & vbNewLine & _
                  "For Example: To Enter a Range A1 to A3 type A1:A3 ")
If ActiveSheet.Range(Cells).Count > 1 Then
  ActiveSheet.Range(Cells).Merge
  MsgBox "Specified cells Merged!"
Else
  MsgBox "For Merging please enter a range containing At-Least 2 or More cells!", vbExclamation
End If
Exit Sub
ErrorHandler:
MsgBox "Entered Range is Invalid!", vbCritical
End Sub

This code asks the user to enter a range, then it merges all the cells present in the specified range.

Recommended Reading: InputBox in VBA, Error Handling in VBA

Merge Cells Across:

In the above methods, we have seen how easy it is to merge cells in Excel. But have you thought how you can merge several individual rows in a range?

Below image will make our objective very clear.

Merge-Cells-Across

If you select all the cells to be merged and then merge them using any of the methods, the output will be like this:

Merge-Cells-Across

Option-1: One obvious solution to this would be selecting each row and then merging them individually. This solution will work but if you have large number of columns then it can be quite cumbersome to merge every row individually.

Option-2: Using the “Merge Across” option. This is an easy way to accomplish our goal and that too in a split second.

Follow the below steps to know how to use this option:

  • First of all select the range that you wish to merge as several individual rows.
  • Next, navigate to the “Home” Tab in Ribbon and click the dropdown arrow on the “Merge and centre” button. Now from the menu Click the option “Merge Across” as shown.

Merge-Across-Option-on Home-tab

  • This will merge the cells as several individual rows.

How to Merge without losing data:

Do you know that merging can sometimes also result into data loss? Yes, you read that right.

See the below image to know how it happens.

Data-Loss-After-Merging

So, what’s the Solution?

The solution to this can be a simple macro that can first concatenate the values in all the selected cells into the upper-leftmost cell and then merge them together.

This boils down to:

Sub JoinAndMerge()
'joins all the content in selected cells
'and puts the resulting text in top most cell
'then merges all cells
Dim outputText As String
Const delim = " "
On Error Resume Next
For Each cell In Selection
  outputText = outputText & cell.Value & delim
Next cell
With Selection
  .Clear
  .Cells(1).Value = outputText
  .Merge
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlCenter
  .WrapText = True
End With
End Sub

This code has been written by Chandoo, you read more ways to tackle this issue on his blog here.

Why Merging sometimes leads to issues:

Merging in excel can cause problems while sorting. If you try to sort your data containing some merged cells, Excel will throw an error saying that, “To do this, all merged cells should be of same size”.

Sorting-Error-For-Merged-Cells

So, I will suggest you to minimize the use of Excel’s merging capability.

Recommended Reading: Find merged Cells in Excel

So, this was all from my side. Do let me know in case you come across any issues while trying to merge cells in Excel.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.
You can find Ankit on FaceBook or Google Plus

Privacy Policy | Disclaimer | About