Unmerge Excel Cells

Unmerge Excel Cells

I saw this on Twitter yesterday: Wanted “Break Apart all merged cells in entire spreadsheet” button. If you’ve done much work in Excel, you’ve probably encountered problems that are the result of merged cells on a worksheet.

Merging cells can seem like a good idea at the time, but can interfere with sorting and filtering, and other things that make an Excel workbook useful. Here’s how you can unmerge Excel cells.

I don’t know what version of Excel that tweeter is using, but there’s an Unmerge Cells command on the Excel Ribbon, and you could add one to the toolbar in earlier versions of Excel.

Unmerge All Cells on All Worksheets

To get rid of all the merged cells in an Excel 2007 workbook, follow these steps:

  • Make a backup copy of the workbook, and store it somewhere safe.
  • Right-click one of the sheet tabs, and click Select All Sheets
  • On the active sheet, click the Select All button, at the top left of the worksheet
  • On the Ribbon’s Home tab, click the drop down arrow for Merge & Center
  • Click Unmerge Cells
  • Right-click one of the sheet tabs, and click Ungroup Sheets
Click Unmerge Cells
Click Unmerge Cells

Unmerge in Excel 2003

To get rid of all the merged cells in an Excel 2003 or earlier workbook, follow these steps:

  • Make a backup copy of the workbook, and store it somewhere safe.
  • Right-click one of the sheet tabs, and click Select All Sheets
  • On the active sheet, click the Select All button, at the top left of the worksheet
  • On the Format menu, click Cells
  • In the Format Cells dialog box, on the Alignment tab, remove the check mark from Merge Cells. If the check box is grey, you’ll have to click twice — click once to add the check mark (in a white box), then click again to remove it.

MergeCellsUnmerge

  • Click OK to close the dialog box.
  • Right-click one of the sheet tabs, and click Ungroup Sheets

And don’t merge those cells again later!

Remember – there are lots more tips, and a few macros, for working with merged cells, on my Contextures site..

_________

0 thoughts on “Unmerge Excel Cells”

  1. Any way to unmerge all Merged & Centered cells and convert them to Centered Across Selection so that the formatting is the same? Can this be done through VBA?

  2. For those using XL2003 and earlier who want to place an Unmerge Button on one of their toolbars, you can assign this macro to the Button…

    Sub UnmergeAllMergedCells()
    Dim WS As Worksheet
    For Each WS In Worksheets
    WS.Cells.UnMerge
    Next
    End Sub

  3. Hmmm… in all Excel versions I use, from 2000 to 2010, whenever I want to unmerge cells, I just click the “merge cells” button again. It’s a toggle switch.

    With either the whole sheet selected (i.e. by clicking the “square” to the left of column A and above row 1, thus selecting all cells in the sheet, — or with an individual merged cell selected, clicking the “merge cells” command on the toolbar/ribbon again will unmerge the cells.

    Why the need for an “unmerge” button on the QAT or a macro? What am I not getting?

    cheers, teylyn

  4. Correction. Excel 2000 does not toggle the merge/unmerge, but from Excel 2003 upwards, just click the Merge (or Merge & Center) icon again and all selected cells are unmerged.

    cheers, teylyn

  5. @Rick Thanks for the code! For Excel 2003 users there’s also an Unmerge button available through Tools>Customize, in the Format category. If you add that to an existing toolbar, you could manually select all the sheets and cells, and click that button to unmerge.

    @Teylyn, the Merge button isn’t on the toolbar by default, and it doesn’t toggle in my copy of Excel 2003. I’ve added both the Merge and Unmerge buttons to my Excel 2003 toolbar.
    Maybe you have a custom button that runs a macro to change the MergeCells property of the selected cells, e.g.
    Sub ChangeMerge()
    With Selection
    .MergeCells = Not .MergeCells
    End With
    End Sub

    Or maybe I’m the one who’s missing something!

  6. In Excel 2000 I used the Unmerge button found under Tools>Customize all the time…what Debra just mentioned.

  7. @Debra… I see from your comment and Jason’s that my code snippet was unnecessary. Personally, I never use Merge, so I never looked to see that Tools/Customize offered that option… on top of which the code was easy to develop so I didn’t even think to look.

  8. For “Squash” and anyone else interested…

    Excel 97 thru Excel 2003 only, do not try this on XL2007.
    Excerpt from “Extras for Excel” add-in by Jim Cone

    ADD CUSTOM BUTTONS TO TOOLBAR…

    Dim SpecialItem as CommandBarControl
    With Application.CommandBars(“Formatting”)
    For Each SpecialItem In .Controls
    If SpecialItem.ID = 402 Or SpecialItem.ID = 1742 Then SpecialItem.Delete
    Next
    Set SpecialItem = .Controls.Add(Type:=msoControlButton, ID:=402, before:=9)
    SpecialItem.OnAction = ThisWorkbook.Name & “!MergeAndAlign”
    SpecialItem.FaceId = 2992
    Set SpecialItem = .Controls.Add(Type:=msoControlButton, ID:=1742, before:=9)
    SpecialItem.OnAction = ThisWorkbook.Name & “!CenterAcrossSelection”
    SpecialItem.Caption = “Center Across”
    SpecialItem.FaceId = 2062
    End With
    ‘–

    REMOVE CUSTOM TOOLBAR BUTTONS…

    With Application.CommandBars(“Formatting”) ‘Formatting Toolbar
    .FindControl(ID:=402).Reset ‘Merge and Center
    .FindControl(ID:=1742).Delete ‘Merge across
    End With
    ‘–

    SUBS TO DO THE WORK…

    ‘Apr 16, 2001 – Created – Jim Cone.
    Sub CenterAcrossSelection()
    On Error GoTo CenterErr
    If TypeName(Selection) = “Range” Then
    If IsNull(Selection.HorizontalAlignment) Or _
    Selection.HorizontalAlignment = xlHAlignCenterAcrossSelection Then
    Selection.HorizontalAlignment = xlHAlignGeneral
    Selection.VerticalAlignment = xlVAlignBottom
    Else
    Selection.HorizontalAlignment = xlHAlignCenterAcrossSelection
    Selection.VerticalAlignment = xlVAlignCenter
    End If
    End If
    Exit Sub
    CenterErr:
    Beep
    End Sub
    ‘–

    ‘Created on April 16, 2001 – Jim Cone.
    Sub MergeAndAlign()
    On Error GoTo Kaput
    If TypeName(Selection) = “Range” Then
    If Selection.Areas.Count > 1 Then
    MsgBox “Select only one area.”, vbInformation, “Merge Cells – Microsoft Excel”
    Exit Sub
    ElseIf Selection.Count > 1 Then
    Application.ScreenUpdating = False
    If Selection.MergeCells Or IsNull(Selection.MergeCells) Then
    Selection.UnMerge
    Selection.VerticalAlignment = xlVAlignBottom
    Selection.HorizontalAlignment = xlHAlignGeneral
    Else
    Selection.Merge
    Selection.VerticalAlignment = xlVAlignCenter
    Selection.HorizontalAlignment = xlHAlignCenter
    End If
    Application.ScreenUpdating = True
    End If
    End If
    Exit Sub
    Kaput:
    Beep
    Application.ScreenUpdating = True
    End Sub
    ‘–

    Jim Cone
    Portland, Oregon USA
    Try the complete add-in… http://tinyurl.com/ExtrasXL
    (it adds a classic menu to xl2007)

  9. @Deb,

    Okay, yes, as a macro, that is true… but then there would be little need to attach it to a button though.

  10. @Squash,

    I think this macro will do what you asked… it only affects merged, centered cells and does not touch other merged cells (you can use my other posted macro to do that afterwards). As with all macros, test it out on a copy, until you are sure it does what you want, as changes made by a macro cannot be undone.


    Sub UnmergeAllMergedCells()
    Dim WS As Worksheet, Cell As Range, Addr As String
    For Each WS In Worksheets
    For Each Cell In WS.UsedRange
    If Cell.MergeArea.Address “” And Cell.Value “” And _
    Cell.HorizontalAlignment xlCenterAcrossSelection Then
    If Cell.HorizontalAlignment = xlHAlignCenter Then
    Addr = Cell.MergeArea.Address
    Cell.MergeCells = False
    With Range(Addr).Rows(1)
    .Select
    .HorizontalAlignment = xlCenterAcrossSelection
    End With
    End If
    End If
    Next
    Next
    End Sub

  11. @Deb… just out of curiosity, is there an HTML tag that I can use to preserve lead spacing when posting code in your blog? As you can see from the code I just posted to Squash, the , combination changes the font, but eats up all the leading (formatting) spaces.

  12. @Squash,

    Don’t pay any attention to the name I used for the macro I posted for your consideration… I used the framework for the first macro I posted in this blog article to develop the code you asked for and forgot to change the macro name… you can, of course, name the macro anything you want.

  13. Rick,

    Thank you for posting your code. Unfortunately, I couldn’t get it to work (are there supposed to be “=” before the double quotes?) so I modified it to get it to do what I wanted for the time being(below). I realize your code also made sure that the cell was both Merged & Centered before demerging it and centering across selection. I’ll probably eventually get it do what yours was doing. I also probably get all other merged cells to be colored so I can manually change them if needed (not everything is worth automating).

    Sub UnmergeAllMergedCellsAndCenterAcrossSelection()
    Dim Cell As Range, Addr As String
    For Each Cell In ActiveSheet.UsedRange
    If Cell.MergeCells = True Then
    Addr = Cell.MergeArea.Address
    Cell.MergeCells = False
    With Range(Addr).Cells
    .Select
    .HorizontalAlignment = xlCenterAcrossSelection
    End With
    End If
    Next
    End Sub

  14. @Squash,

    Yes, there are THREE missing operators, but it is NOT the equal signs that are missing… it is the “not equal” signs that got eaten by Deb’s comment processor. I’m using the letters NEQ for the “not equal” sign (less than symbol followed by a greater than symbol)… make that substitution in the line below and it should make the code I posted work as intended….

    If Cell.MergeArea.Address NEQ “” And Cell.Value NEQ “” And _
    Cell.HorizontalAlignment NEQ xlCenterAcrossSelection Then

    Note… in case you are not familiar with it, this is NOT two separate lines of code, it is one line of code that has been “continued” across two displayed line (that is what the space/underbar does at the end of the first line).

  15. @Debra,
    I use the “merge and center” button on the Excel 2003 Formatting toolbar. It’s there by default and I have not changed it or assigned it any macros. I have Excel 2003 on several different computers, and the behaviour is the same on all machines.
    Select the merged cell or select the whole worksheet and click the button. All merged cells will be unmerged.

    In Excel 2007 and Excel 2010 it’s a single click on the “Merge & Center” icon on the Home ribbon. Again, I have no special macros running for those and I have these Excel versions on different computers, where they all behave the same.

    In fact, un-merging is the only reason for me to use this toolbar button / ribbon command. I prefer “center across selection”, for obvious reasons, but whenever I get a spreadsheet from elsewhere, or a badly designed BI repot exported into Excel, the first thing I do is select all cells and hit that icon to get rid of the merged cells.

    Maybe it’s the New Zealand air.

    cheers, teylyn

  16. @teylyn, ahhh…the Merge and Center button…now I see what you mean, thanks. I always remove that from the toolbar and add the Unmerge button instead. But, if the Merge and Center button does an unmerge too, then it finally has a use!

  17. @Rick I’ll don’t know off hand how to keep the indentation, and will see what I can find out.

    Maybe you can use < (ampersand lt semi-colon) to create a less than symbol and > (ampersand gt semi-colon) to create a greater than symbol

    And probably the pre tags don’t work in the comments

  18. New question: I have Excel 2010 and have noticed that when I click on a single cell to enter data, several cells in that column highlight. This is becoming annoying because if I only want to copy one or a few cells in the column I am unable to select them without several others below also auto-highlighting for selection. How can I turn this off?
    Thanks,
    Laura

  19. Question: I have a pivot table and I would like to apply Excel’s Alignment on the Tool Bar to center two fields. I am looking for a VBA code to center (align cells to the center) Pivot Table cells (for example range B3:B30,C3:C30).

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.