How to Find a Merged Cell in Excel

After you merge cells, they no longer behave like normal cells and often result in issues when sorting or using inside a formula. So, it’s better to avoid using them in the first place.

Even if your worksheet already contains such cells, it’s quite easy to identify them as they are larger than normal cells. But, manually looking for such cells in a large dataset can be a tedious job.

Instead, you can use tools like the Find and Replace feature and VBA to quickly find and select all of them at once.

Using Find and Replace 

Along with finding text/number characters, the Find and Replace can also search for cells with specific formatting like merged cells. Once you find them, selecting is just a piece of cake.

1. Select all the cells that may contain merged cells. You can use the shortcut key Ctrl + A for this.
2. Then, press Ctrl + H.
3. On the Find and Replace prompt, click Format in the Find what field.
   
4. Next, select the Alignment tab.
5. Under the Text control section, uncheck all the options except the Merge cells checkbox. 
   
6. Click OK.
7. Click Find Next to go through each merged cell individually.
   
8. To select all the merged cells, click Find All.
9. Then, select the first match and click the last one while pressing the Shift button.
   
10. Click Close.

If the above steps didn’t select the merged cells, they are probably using the Center across selection formatting, which makes cells appear merged. To find such cells, follow the same steps as mentioned above. But, this time, replace steps 5 and 6 with the two steps below.

1. Repeat the same four steps as mentioned above.
2. Select the Center Across Selection option under the Horizontal section. Also, uncheck options under the Text control section.
   

Using VBA Code

Another way to find the merged cells is by executing a VBA code on your worksheet. Once the code selects all the merged cells, you can unmerge them all at once.

For this,

1. Open the worksheet where you want to find merged cells.
2. Right-click the worksheet and select View code.
   
3. On the new window, copy and paste the code mentioned below.
   

Sub SelectAllMergedCells()

Dim c As Range
Dim mergedCells As Range
Dim fullRange As Range
Dim rangeDescription As String

If Selection.Cells.Count > 1 Then
    Set fullRange = Selection
    rangeDescription = "selected cells"
Else
    Set fullRange = ActiveSheet.UsedRange
    rangeDescription = "active range"
End If
For Each c In fullRange
    If c.MergeCells = True Then
        If mergedCells Is Nothing Then
            Set mergedCells = c
        Else
            Set mergedCells = Union(mergedCells, c)
        End If
    End If
Next
If Not mergedCells Is Nothing Then
    mergedCells.Select
Else
    MsgBox "There are no merged cells in the " _
        & rangeDescription & ": " & fullRange.Address
End If
End Sub

4. Press F5 or click the Run button below the menu bar.
   
5. Once Excel finishes executing the code, the merged cells are selected like in the image below.
   
6. To unmerge such cells, click the Merge & Center button under the Home tab.