How to Find All Merged Cells in Excel

The "something in here is merged and I can't find it" problem

You try to sort a column and get "This operation requires the merged cells to be identically sized." You build a pivot table and half your labels vanish. Somewhere in 4,000 rows there's a merged cell, and Excel won't say where.

There's a proper way to find merged cells in Excel. It takes about ten seconds once you know where the setting hides, which isn't where most people look first.

TL;DR: Key takeaways

• Find and Replace can filter by format. Set it to Merge cells and click Find All to list every merged cell.
• Go To Special does not have a merged-cells option. That's the wrong tool, and the reason most searches for this go nowhere.
• Switch Within to Workbook to scan every sheet in one pass.
• Excel for Mac and the web don't offer the format filter; use the VBA snippet below instead.

Method 1: Find and Replace with a format filter

1. Ctrl + F, click Options, then Format.
2. Alignment tab, tick Merge cells, click OK.
3. Leave Find what empty, click Find All.

Excel lists every merged cell; clicking a row jumps to it. Microsoft documents this in its guide to finding merged cells. Leave the search box empty (a value in it only finds cells matching that text) and change Within to Workbook to cover every tab in one search.

Nearly everyone tries Go To Special first, since that's where Excel keeps its other "find all the X" tools. It covers blanks, formulas, comments, and conditional formats, but it has never had a merged-cells option. That gap is most of the reason this gets searched so often; the real answer lives one dialog over.

Clear the filter afterward (Format > Clear Find Format) or Excel keeps applying it to your next search.

Method 2: List every merged range with VBA

Windows-only for the format filter. On Mac or the web, or for a scriptable list, use this instead:

Sub ListMergedCells()
    Dim ws As Worksheet, cell As Range, found As String
    For Each ws In ActiveWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If cell.MergeCells Then
                If cell.Address = cell.MergeArea.Cells(1, 1).Address Then
                    found = found & ws.Name & "!" & cell.MergeArea.Address & vbCrLf
                End If
            End If
        Next cell
    Next ws
    MsgBox IIf(Len(found) = 0, "No merged cells found.", found), vbInformation
End Sub

Paste into a module (Alt + F11, Insert > Module) and run. The MergeArea check matters: without it, a merge spanning six cells reports six times. On a sheet with a few hundred thousand cells, expect a noticeable pause.

What merged cells actually break

Sorting fails outright. Filtering returns only the merge's first row and hides the rest. Pivot tables treat the label as one value with blanks underneath, fragmenting groups. VBA and formulas see the value only in the top-left cell, A2:A7 merged as "North" means A3 through A7 are genuinely empty, so a lookup against A5 returns blank.

Once you've found them, fix them

Unmerging alone leaves the same problem in a different shape: value in the top cell, blanks below. Unmerge Cells and Fill Down does both in one pass, copying the top-left value into every cell the merge spanned. Full walkthrough in how to unmerge cells and fill down.

If a heading centered over columns is all you wanted, Ctrl + 1 > Alignment > Center Across Selection looks identical to a merge without breaking sort, filter, or VBA. Old merges often leave inconsistent fills and borders behind too; Remove All Formatting clears that in one pass if you'd rather start clean.

Frequently asked questions

How do I find merged cells in Excel?

Press Ctrl + F, click Options > Format, tick Merge cells on the Alignment tab, and click Find All with the search box empty. Excel lists every merged cell and jumps to it on click.

Why doesn't Go To Special find merged cells?

Excel never built that option there. Go To Special covers blanks, formulas, and conditional formats, but merged cells aren't on the list. The format filter inside Find and Replace is the actual built-in route.

How do I find merged cells across all sheets at once?

Change Within from Sheet to Workbook in the expanded Find and Replace dialog before clicking Find All.

How do I find merged cells in Excel on a Mac?

The Mac Find dialog has no Format filter. Run the VBA snippet above, or clean the file in Excel for Windows and save it back.

Why do merged cells break sorting and filtering?

Sorting requires every cell to be the same size; a merge violates that and triggers the error directly. Filtering returns the merge's first row and silently hides the rest.

What to do next

Run the Find and Replace format filter with Within set to Workbook to see how bad it is. If it's bad, grab the free macro and unmerge and fill in one pass.