How to find merged cells in excel

Answer

To find merged cells in Excel, use the built-in selection and search tools that identify cells with the Merge property. Follow one of these methods depending on what you need: select them visually, locate their addresses, or produce a list.

Method 1 — Use Go To Special (fastest way to select merged cells)

1. Select the worksheet (click any single cell to activate the sheet).

2. On the ribbon, go to Home > Find & Select > Go To Special.

3. In the Go To Special dialog, select Merged cells.

4. Click OK.

5. Excel selects every merged area on the active sheet so you can format, clear, or inspect them.

Windows shortcut note: Press Ctrl+G then click Special..., or press F5, then Special....
Mac shortcut note: Press Command+G then click Special....

Method 2 — Use Find (search for cells with the Merge format)

1. Press Ctrl+F (Windows) or Command+F (Mac) to open Find.

2. Click Options to expand settings.

3. Click Format....

4. In the Format Cells dialog go to the Alignment tab.

5. Check Merge cells and click OK.

6. Leave Find what blank to match any content or enter text to search within merged cells.

7. Click Find All to see a list of every match and their addresses, or click Find Next to jump through them.

Difference for Mac: The dialog labels and layout are the same; use Command+F to open Find and then Options.

Method 3 — Use a short VBA macro to list merged areas (produce addresses)

1. Press Alt+F11 (Windows) or Option+F11 (Mac) to open the Visual Basic Editor.

2. Insert a new module: Insert > Module.

3. Paste the macro below and run it (select the sheet to scan before running).

   Sub ListMergedCells()

       Dim ws As Worksheet, outWs As Worksheet

       Dim a As Range, mergedArea As Range

       Dim r As Long

       Set ws = ActiveSheet

       On Error Resume Next

       Application.ScreenUpdating = False

       Set outWs = Worksheets.Add(After:=Worksheets(Worksheets.Count))

       outWs.Name = "MergedCellsList"

       outWs.Range("A1:D1").Value = Array("Merged Area", "Address", "Top-left Value", "Row Count x Col Count")

       r = 2

       For Each a In ws.UsedRange

           If a.MergeCells Then

               Set mergedArea = a.MergeArea

               outWs.Cells(r, 1).Value = mergedArea.Address

               outWs.Cells(r, 2).Value = "'" & mergedArea.Address(External:=False)

               outWs.Cells(r, 3).Value = mergedArea.Cells(1, 1).Value

               outWs.Cells(r, 4).Value = mergedArea.Rows.Count & " x " & mergedArea.Columns.Count

               r = r + 1

               Set a = mergedArea.Cells(mergedArea.Cells.Count) 'skip to end of area

           End If

       Next a

       Application.ScreenUpdating = True

       On Error GoTo 0

       MsgBox "Finished. See sheet: " & outWs.Name, vbInformation

   End Sub

4. Run the macro with the cursor inside the sub and press F5 or choose Run > Run Sub/UserForm.

5. The macro creates a new sheet named MergedCellsList that lists every merged area address, the top-left value, and its size.

Method 4 — Visual inspection tips (useful when there are few merges)

1. Turn on Gridlines and cell borders to make merged areas more obvious.

2. Use Home > Alignment > Wrap Text and Center across selection alternatives to avoid accidental merges.

Which method should I use?

To select merged cells quickly, use Go To Special.
To get a clickable list of where merged cells live, use the Find dialog with the Merge format and Find All.
To generate a permanent inventory, run the provided VBA macro.

Common pitfalls and best practices

1. Merged cells break many functions such as sorting and some pivot table operations; avoid merging when possible.

2. Use Center Across Selection (Format Cells → Alignment) as a safer visual alternative to merging.

3. When removing merges, clear contents of merged areas first if you need to preserve data.

4. Remember that a merged area’s displayed value comes from its top-left cell only.

How to unmerge once you find them?

To unmerge selected merged cells:

1. Select the merged cells (use Go To Special or Find).

2. On the ribbon go to Home > Merge & Center and click to toggle off merging, or choose Merge & Center dropdown > Unmerge Cells.

Windows vs Mac differences

1. Keyboard shortcuts differ: use Ctrl on Windows and Command on Mac for Find/Go To.

2. Dialog layouts are equivalent; the path names on the ribbon remain Home > Find & Select and Home > Merge & Center on both platforms.

3. VBA runs in both but on Mac you might need to enable Developer tools and assign permissions for macros.

Answers to likely short questions

Are merged cells easy to find?

Yes. Use Go To Special > Merged cells to select them instantly.

Can formulas detect merged cells directly?

No. Formulas cannot reliably detect merged-cell formatting across a sheet; use Find/Go To Special or VBA for accurate detection.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report