Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Identifying Merged Cells.

Identifying Merged Cells

Written by Allen Wyatt (last updated April 8, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


11

Alan asked if there is a way to quickly and easily identify which cells are merged in a workbook created by someone else. There are, in reality, several different ways you can go about identifying these cells.

One method for identifying the cells is to use Excel's searching capabilities. Follow these steps:

  1. Press Ctrl+F. Excel displays the Find tab of the Find and Replace dialog box.
  2. If necessary, click the Options button to make sure the Find and Replace dialog box is expanded to show all options. (See Figure 1.)
  3. Figure 1. The Find tab of the Find and Replace dialog box.

  4. Make sure the Find What box is empty.
  5. With the insertion point in the Find What box, click the Format button. Excel displays the Find Format dialog box.
  6. Make sure the Alignment tab is displayed. (See Figure 2.)
  7. Figure 2. The Alignment tab of the Find Format dialog box.

  8. Make sure the Merge Cells check box is selected (there should be a check in the check box).
  9. Click OK to close the Find Format dialog box.
  10. Click Find All.

Excel searches for any merged cells and if they are located, the cells are displayed in the bottom of the Find and Replace dialog box. You can then select one of the found ranges and the corresponding range is selected in the worksheet.

If you prefer, you can a macro to find the various merged cells in the worksheet. The following macro shows perhaps the simplest method of doing this:

Sub FindMerged1()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange
        If c.MergeCells Then
            MsgBox c.Address & " is merged"
        End If
    Next
End Sub

This particular macro steps through all the cells in the worksheet (well, at least those that are in the UsedRange) and, if the cell is part of a merged cell, a message box is displayed. Note that the pertinent property being checked is the MergeCells property. This is set to True if the cell is merged with another cell.

Of course, a macro such as this can take quite a long time to run if the worksheet has lots of cells and even longer if a good number of those cells are merged. Your macro would run faster if it didn't stop at each merged cell and display a dialog box. The following version takes a different approach, filling each merged cell with a yellow color:

Sub FindMerged2()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange
        If c.MergeCells Then
            c.Interior.ColorIndex = 36
        End If
    Next
End Sub

A variation on this approach could be to create a user-defined function that simply returns True or False if the cell is merged:

Function FindMerged3(rCell As Range)
    FindMerged3 = rCell.MergeCells
End Function

With this simple function you could then use conditional formatting to somehow highlight cells if they are merged. (If the function returns True, then conditional formatting applies whatever formatting you specify to the cell.)

Finally, if you want a list of cells that are merged in the worksheet, you can simply have your macro put together the list instead of coloring the cells:

Sub FindMerged4()
    Dim c As Range
    Dim sMsg As String

    sMsg = ""
    For Each c In ActiveSheet.UsedRange
        If c.MergeCells Then
            If sMsg = "" Then
                sMsg = "Merged worksheet cells:" & vbCr
            End If
            sMsg = sMsg & c.Address & vbCr
        End If
    Next
    If sMsg = "" Then
        sMsg = "No merged worksheet cells."
    End If

    MsgBox sMsg
End Sub

This variation displays a single message box at the end of the macro, indicating the addresses of any merged cells located in the worksheet.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12385) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Identifying Merged Cells.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Special Characters in Pattern Matching

The most powerful search engine in Word uses pattern matching, but the way you specify special characters in a ...

Discover More

Positioning a Column on the Screen

If you have static columns and dynamic columns on the screen, you may want the dynamic columns to always show a ...

Discover More

Specifying a Paper Tray in a Macro

You may want to use a macro to process and then print your document. Part of that printing may involve specifying which ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Removing Borders

Need to get rid of the borders around a cell? The shortcut in this tip can make quick work of this formatting task.

Discover More

Setting Orientation of Cell Values

Need the contents of a cell to be shown in a direction different than normal? Excel makes it easy to have your content ...

Discover More

Using an Exact Number of Digits

Excel allows you to format numeric data in all sorts of ways, but specifying a number of digits independent of the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 3?

2023-04-18 11:43:17

J. Woolley

My Excel Toolbox now includes the FindMergedCells macro to report all merged cells in the active worksheet or in all worksheets of the active workbook. The results can be copied to the clipboard as hyperlink formulas or as plain text so they can be pasted into a worksheet or other document. The macro uses the Find method illustrated in my most recent comment below.
See https://sites.google.com/view/MyExcelToolbox/


2023-04-16 10:44:23

J. Woolley

The Tip's first suggestion (Find) inspired the following macro, which should be faster than FindMerged4 because it doesn't have to examine every cell in UsedRange:

Sub FindMerged5()
    Dim rSheet As Range, rFound As Range, sMsg As String, sFirst As String
    Const MaxMsg As Integer = 768
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    Application.FindFormat.Clear
    Application.FindFormat.MergeCells = True
    Set rSheet = ActiveSheet.Cells
    Set rFound = rSheet.Find(What:="", After:=rSheet(1), _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=True)
    If rFound Is Nothing Then
        sMsg = "There are no merged cells in the active worksheet."
    Else
        sFirst = rFound.Address
        Do
            sMsg = vbLf & rFound.MergeArea.Address & sMsg
            Set rFound = rSheet.Find(What:="", After:=rFound, _
                SearchDirection:=xlPrevious, SearchFormat:=True)
        Loop Until rFound.Address = sFirst
        sMsg = "Merged cells in the active worksheet:" & sMsg
    End If
    Application.FindFormat.Clear
    If Len(sMsg) > MaxMsg Then
        sMsg = Left(sMsg, MaxMsg)
        sMsg = Left(sMsg, InStrRev(sMsg, vbLf)) & "..."
    End If
    MsgBox sMsg
End Sub

Notes:
1. The macro works last-to-first but displays first-to-last.
2. Range.FindNext does not work as expected when SearchFormat is True.
3. Microsoft's document does not explain that Application.FindFormat returns a CellFormat object.


2023-04-13 14:18:54

J. Woolley

My Excel Toolbox includes the MergedCellsCF macro to toggle conditional formatting (CF) that highlights all of the active sheet's merged cells. The default highlight color is pale-yellow, but this can be changed by the user (Home > Conditional Formatting > Manage Rules...). One color applies to all sheets. If the merged cells CF is OFF when the macro is run, it will be toggled ON; otherwise, it will be toggled OFF and its most recent highlight color will be saved for the next ON. When the merged cells CF is ON, it takes priority and prevents other CF activity among the sheet's merged cells. (Notice the Tip's FindMerged2 macro fills each merged cell with a permanent color, but the merged cells CF can be toggled ON/OFF.)
The merged cells CF utilizes the following function in My Excel Toolbox:
=IsMergedCell([Target])
That function returns TRUE if the first cell in Target is merged. Target is optional; its default is the formula's cell.
An add-in's function cannot be directly referenced in a CF's formula, but that formula can reference a defined name that refers to the add-in's function; therefore, the MergedCellsCF macro defines the following:
    Name: MergedCell
    Scope: Workbook
    Refers to: =IsMergedCell()
    Comment: TRUE if formula's cell is merged; see My Excel Toolbox.
Once a MergedCell name has been defined in a workbook (manually or by macro), it will persist.
See https://sites.google.com/view/MyExcelToolbox/


2023-04-11 09:30:03

J. Woolley

For more about "center across selection," see my recent comment about the CenterAcrossSelection macro in My Excel Toolbox here: https://excelribbon.tips.net/T012183_Creating_a_Center_Across_Selection_Button.html
For something like "center across selection vertically," see my recent comment about the FillJustifyText macro in My Excel Toolbox here: https://excelribbon.tips.net/T011496_Combining_Multiple_Rows_in_a_Column.html


2023-04-10 21:52:32

Tomek

@Kiwerry,
I think the suggestions from the comments below will help you with the conversion you wanted. So rather than reinventing the solution, I wanted to comment on the behaviour of Center Across Selection.
1. It will not work if it is superimposed on the merged cells; they will stay merged, but any additional cells selected will have the horizontal alignment set to Center Across Selection. See later for the importance of this.
2. There is no equivalent to this if you want the contents centered vertically over several cells in a column. This alignment only works in individual rows.
3. There is a quirky way the cell contents are displayed over the range of cells with this formatting applied to them:
      a. if you enter a value in the leftmost cell of the range it will be centered across the whole range selected when this formatting is applied.
      b. if you enter another value to the right of the first one, the two entries will be centered in their own sections. The first value will be centered in the range of cells to the left of the second entry cell, and the second in the remaining range.
      c. if you remove the second entry the first will jump to be centered again across the originally selected range.
      d. this behaviour will be the same whether you applied the format to the range at once or individually to the cells within the range. All adjacent cells having this attribute will be treated as if it was one range to center over. If you apply this formatting to a range of empty cells immediately to the right of already formatted ones, the original entry will become centered over the whole range.
      e. If you merge some of the cells that had this attribute, the remaining cells will retain the attribute, but if you then un-merge the cells those will not re-acquire the Center Across the Selection attribute. This means that you have to un-merge the cells before applying Center Across Selection.

I know this is mostly academic, but hope it may help prevent some confusion.


2023-04-10 11:59:30

J. Woolley

@Alex Blakenburg
Very clever. You have used the default property of a Range object, which is Item. Since c is a single cell and c.MergeArea returns a Range, c.MergeArea(1) is equivalent to c.MergeArea.Item(1), which is the first cell in the merged range. Since only one cell is the first cell, the merged area is only recorded once and other cells c in the merged range are ignored.


2023-04-10 08:59:19

Kiwerry

Thank you all for your comments; We have a flight from the Antipodes to Europe tomorrow and I will be able to try them once we have adapted to the time change of about ten hours.


2023-04-10 05:15:13

Alex Blakenburg

Speaking of Merged Areas, it would probably be useful for the last example showing the Merged Cells in a message box to condense it and only show the address of the merged areas.

Sub FindMergedAreas()
Dim c As Range
Dim sMsg As String

sMsg = ""
For Each c In ActiveSheet.UsedRange
If c.MergeCells Then
If c.Address = c.MergeArea(1).Address Then
If sMsg = "" Then
sMsg = "Merged worksheet cells:" & vbCr
End If
sMsg = sMsg & c.MergeArea.Address & vbCr
End If
End If
Next
If sMsg = "" Then
sMsg = "No merged worksheet cells."
End If

MsgBox sMsg
End Sub


2023-04-09 10:21:52

J. Woolley

@Kiwerry
See Range.MergeArea property: https://learn.microsoft.com/en-us/office/vba/api/excel.range.mergearea


2023-04-09 10:19:33

Tomek

@Kiwerry
Interesting question. I take it as a challenge, but cannot do the testing until I am back home (Mon. Or Tue.)


2023-04-08 20:43:06

Kiwerry

Interesting, thank you Allen.
As you say, merged cells can be a nuisance and I often find myself using “Center across Selection” instead. Your article raises the possibility of adapting your first macro to replace the Merged formatting with “Center across Selection”. However, in order to do so it would be necessary to identify the range of merged cell, i e. with which neighbouring cells is it merged? Does VBA provide a way of doing this, or would it be necessary to program it?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.