How to Find Merged Cells in Excel

Many times, you get data that has merged cells which stops you to perform some activities like data sorting, using the reference to the merged cells in formulas, and using the filter selection properly.

In that case, you must find the merged cells to unmerge and adjust them, and finding the merged cells one by one could be a time-consuming task.

But, just sit and relax, Excel has the functionality to find and highlight all the merged cells in one go to overview them quickly before unmerging them and we have described that functionality for you.

Steps to Find Merged Cells Using Find and Replace

1. First, select the cell range or the entire sheet from where you want to find the merged cells.
2. Once the range got selected, go to the “Home” tab and under the “Editing” group click on the “Find & Replace” icon and select Replace option to open the Replace dialog box.
3. Or simply press the “Ctrl + H” shortcut key to open the “Replace” dialog box.
   
4. After that, click on the “Format” option in the “Find and Replace” dialog box and you will get the “Find Format” dialog box opened.
   
5. In the “Find Format” dialog box, go to the “Alignment” tab and tick mark the “Merge cells” option and untick the “Wrap text” and “Shrink to fit” options properly as shown in the image, and click OK.
   
6. Now, click on “Find All” in the “Find and Replace” dialog box and you will get all the merged cells listed below in the dialog box.
   
7. Now, click on any listed cell in the “Find and Replace” dialog box and select all the listed cells using the “Ctrl +A” keys and your merged cells in the data will get selected.
   
8. To highlight the selected merged cells, go to the “Home” tab and under the “Font” group, click on the “Fill Color” icon and select the color, and your merged cells will get highlighted and you can overview them quickly before unmerging them.
9. In the end, to unmerge them, just click on the “Merge & Centre” icon under the “Alignment” group and all the merged cells will get unmerged.
   

Find Merged Cells Using a VBA Code

Users can also use a VBA code to find and highlight the merged cells.

Sub find_merged_cells()
    Dim Cel As Range, Msg As String, Addr As String
    For Each Cel In ActiveSheet.UsedRange
        If Cel.MergeCells Then
            Cel.Interior.Color = 65535
        End If
    Next
End Sub