How to Merge Cells in Excel

Whenever you have a long value, especially a text, you might want to span it across multiple cells and merge cells to make the data more presentable. 

As the name speaks for itself, the most obvious option is using the Merge & Center option that’s easily accessible under the Home tab.

However, there are some overlooked options like Center Across Selection and Justify cells, which help to display as if the cells have been merged without actually merging them.

Using Merge & Center 

The Merge & Center option lets you merge multiple cells into one. If you expand its dropdown menu, you can find other additional options like—Merge Across and Merge Cells. 

However, note that it could sometimes lead to several issues like when sorting data, using conditional formatting, or using it in a formula.

Regardless of the issues, this method is appropriate as long as you use it to merge cells for a header or a footer.

1. Select the cells you want to merge. Here, we want to merge the highlighted cells for the header “Sales report January 2023”.
   
2. Click the down arrow icon next to Merge & Center button under the Home tab.
   
3. Choose one of the following options.
   • Merge & Center: merges cells aligning the cell contents at the center (Shortcut key: Alt + H + M + C)
     
   • Merge Across: only merges adjacent cells across the column (doesn’t work vertically) and aligns the cell contents to the leftmost cell (Shortcut key: Alt + H + M + A) 
     
   • Merge cells: merges cells and align the contents vertically or to the leftmost cell at the bottom depending on how many cells are selected(Shortcut key: Alt + H + M + M)
     
4. To unmerge the above cells, simply select them and click the Merge & Center button. Or, select Merge & Center > Unmerge Cells.
5. Additionally, you can use the Find and Replace tool to find all the merged cells in your worksheet.

Note: The above methods only keep/preserve the upper-left value while merging multiple cells. So, make sure you don't merge cells with data in other cells as they can get lost in the process.

Using Center Across Selection

The Center Across Selection is a much better alternative to merging cells than the above method. With this method, it will appear as if the cells have merged, but only one cell actually contains the value.

1. Select the cells you want to merge or span across.
   
2. Press Ctrl + 1 to open the Format Cells dialog box.
3. Then, select the Alignment tab.
4. Next, click to expand the options below the Horizontal field and choose the Center Across Selection option.
   
5. Click OK.
6. Now, the merged cells will look like below.
   

Note: You can only select cells across columns but not vertically using this method.

Using Justify Fill

Another great alternative to Merge & Center is the justify fill option. It is particularly useful if you want to span a long text across multiple rows and columns without actually merging them.

The good part is, you get to select how many cells the text will span without modifying the original column width.

1. Select the cell containing the long text along with the adjacent cells where you want to span it. Make sure the adjacent cells are empty.
   
2. Go to the Home tab and select Fill > Justify.
   
3. Now, the final output is as follows.
   

Alternatively, you can use this method to merge texts in multiple cells across rows to fit in a single cell. However, you will have to adjust the column width for this particular case.

1. Hover over the main column header. Once it turns to a double-arrow icon, drag it across columns such that all the text fits into the same cell.
   
2. Now, select the cells that contain the texts.
   
3. Then, click Fill > Justify under the Home tab.
   
4. The final output is as follows.
   

How to Merge Cells without Losing Data?

Since the Merge & Center option only takes values from the upper-left cell, values from the remaining cells are lost while merging. To avoid this issue, you can use the VBA method as mentioned below.

This method still utilizes the Merge and Center option in its code. So, you might get the warning. However, the benefit of using this method is that it saves all the contents beforehand to avoid losing data after merging.

1. Select the cell(s) you want to merge.
   
2. Right-click on the current worksheet and select View Code.
   
3. On the main window, copy and paste the following code.
   

Sub mergeCellsWithoutLosingData()
 
Dim cellValue As String
Dim mergeCells As Range

Set mergeCells = Selection
For Each Cell In mergeCells
    cellValue = cellValue & " " & Cell.Value
Next Cell

With mergeCells
    .Merge
    .Value = Trim(cellValue)
    .WrapText = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
End Sub

4. Click the Run icon below the menu bar.
   
5. Now, the merged cells look as follows.
   

Related Questions

Why is the Merge & Center Button Greyed Out?

The Merge & Center appears greyed out and doesn’t work in two particular cases. One is when you try to select a merged cell and a regular cell together. Likewise, the button won’t work when selecting a cell that’s part of a table.

How Do I Merge Values in Two or More Cells?

If you just want to merge values from multiple cells, you don’t need to merge the cells. You can just combine them using a function like TEXTJOIN or CONCAT.