Guide to Merging Cells in Excel 2010, 2013, 2016

When working with Excel, there's often a need to merge cells for a larger, more organized layout. Learn how to merge cells in Excel 2016 with this tutorial. For Excel 2013 and 2010, the process is quite similar.

Discover three effective methods for merging cells in Excel without the risk of data loss. Follow along for a seamless merging experience.

Method 1: Merging Cells in Excel using Merge & Center Feature

Merging cells using this method is quick and effective, especially when cells either lack data or have data only in the first cell to be merged. Follow these steps:

Step 1: In the Excel file, select the cells you want to merge.

Step 2: Next, go to the Home tab. Under Alignment, choose Merge & Center to merge the selected cells.

This action will merge the selected cells into one, with the data in the upper-left cell centered within the merged cell.

Additionally, you have two other options for merging cells when clicking on the triangle icon next to Merge & Center:

• If you choose Merge Across, the selected cells will merge along each row. The data in the first cell of each row you selected will be retained and displayed from the beginning of the merged cell.

• If you choose Merge Cells, the selected cells will be merged into one, and the data in the upper-left cell will be preserved but not centered, unlike when you choose Merge & Center.

Method 2: Merging Cells in Excel using VBA Code

By utilizing this method, you can swiftly merge the selected cells without losing data. Follow these steps:

Step 1: On the Excel file, select the cells you want to merge. Next, go to the View -> Macros tab or press the shortcut Alt + F8 to open the Macro window.

Step 2: In the Macro window, enter a name for the Macro in the Macro name section and click Create.

Note: The macro name should be written without spaces or accents.

Step 3: In the Visual Basic for Applications window, paste the code snippet below into the Sub .... End Sub section. Then, click the green Run icon or choose Run -> Run Sub/UserForm to execute the Merge Cells Macro.

Dim Cell As Range, Temp As String
    On Error Resume Next
    If Selection.MergeCells = False Then
        For Each Cell In Selection
            If Cell <> '' Then Temp = Temp + Cell.Text + ' '
        Next Cell
        Selection.Merge
        Selection.Value = Left(Temp, Len(Temp) - 1)
    Else
        Selection.UnMerge
    End If
     Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter

Upon receiving the confirmation prompt, click OK. Then, close Visual Basic. Your selected cells are now merged.

If you want to merge other cells in the current Excel file, simply select (highlight) the cells to be merged. Then, go to View -> Macros or use the shortcut Alt + F8.

Open the Macro dialog box by double-clicking on the newly created Merge Cells Macro.

That's it! You have successfully merged the cells.

Method 3: Merging Cells in Excel using Concatenate Function and Merge & Center.

This method allows you to merge cells in Excel without losing data. However, an additional step involves using the Concatenate function to merge data before utilizing Merge & Center to combine cells. Follow these steps:

Step 1: Choose any cell to input the CONCATENATE function for merging data in the cells you want to combine.

This function is fairly straightforward; if you're unfamiliar, you can learn more about using the CONCATENATE function here.

For instance, if you want to merge cells B7, C7, D7; in cell E7, input the CONCATENATE function as follows =CONCATENATE(B7;' ';C7;' ';D7)

That's it! You have merged the data in cells B7, C7, D7 separated by a space. You can customize the CONCATENATE function as needed.

Step 2: Once you've merged the data, copy it from the merged cell to the first cell among the cells to be merged.

For example, copy the data in the cell containing the CONCATENATE function. Then, select cell B7 (the first cell among the cells to be merged), right-click, and choose Value in Paste Options to paste only the values without the formula.

This way, all the data will be transferred to the position of the leftmost cell. Next, you can delete the CONCATENATE function.

Step 3: Merge Cells.

Step 4: Once you've transferred all the data to the first cell, select the cells you want to merge. Then, choose Merge & Center, Merge Across, or Merge Cells based on your preference.

By doing so, you will merge cells in Excel without losing any data.

Here are three methods to merge cells in Excel that you can apply when needed. Hopefully, this article proves helpful to you. Wishing you success!