How to Unmerge All Cells in Excel?

by

It is often tempting to combine several related cells of your worksheet into one by merging them together.

It could be for aesthetics and alignment or it could be a formatting requirement. The real problem arises when you want to perform some specific tasks on your worksheet.

You’ll find that it is impossible to sort, filter, and sometimes even select cells that are part of a merged cell.

What’s worse, if you have a lot of such merged cells, it might be really difficult to identify and unmerge these one by one (especially when you have lots of these in a large dataset).

In this tutorial, I will show you how to find, identify and unmerge all the merged cells in your Excel worksheet.

Quickest & Easiest Way to Unmerge all Cells in the Worksheet in Excel

Perhaps the quickest way to unmerge all cells in Excel is by selecting all the cells and using the Merge and Center button.

This way is especially helpful if you don’t really care about which cells are unmerged and are just looking for a way to quickly unmerge everything.

Let’s suppose you have the following worksheet:

Dataset with merged cells

Notice there are a lot of merged cells in this sample sheet, for example, cells A6:A7, B3:B4, and B8:B9.

To unmerge all cells quickly follow the steps outlined below:

  1. Select all the cells by pressing CTRL+A on the keyboard or clicking on the Select All Cells button (located at the top left corner of your worksheet), as shown below:
Select the light gray triangle at the top left to select all cells
  1. From the Home tab, under the Alignment group, click on the dropdown arrow next to Merge & Center.
Click on the Merge and Center option
  1. Select Unmerge Cells from the dropdown list that appears.
Click on Unmerge cells

How to Find all the Merged Cells with Find and Replace

If you would rather have more control over which cells are unmerged, you can use the following method to identify and find all the merged cells in your worksheet.

In Excel, the Find and Replace feature lets you find and select merged cells quickly and easily. Here’s how you can use it to find and unmerge your required cells in Excel:

  1. Select all the cells by pressing CTRL+A on the keyboard or clicking on the Select All Cells button.
  2. From the Home tab, under the Editing group, click on the ‘Find and Select’ dropdown.
Click on Find and Select
  1. Select Replace from the dropdown list that appears.
Click on Replace
  1. This will open the Find and Replace dialog box.
  2. Select Options. This will display more Find and Replace options.
Click on the OPtions button in Find and Replace
  1. Click on the Format button next to the ‘Find What’ field. This will open the ‘Find Format’ dialog box.
Click on the Format option for Find what
  1. Select the Alignment tab and make sure the box next to ‘Merge Cells’ is checked.
Check the Merge cells option in the Find Format dialog box
  1. Click OK.
  2. You should be back to the Find and Replace dialog box now. In this box, click ’Find All’. 
Click on Find All
  1. You can now see a list of all the merged cells in the worksheet.
All merged cells are shown
  1. You can choose to select all these merged cells by pressing CTRL+A or select only the cells that you want to unmerge.
Select all the merged cells found by Find and Replace
  1. Press the Close button to close the dialog box.

Your merged cells are now selected and you can go ahead and unmerge them by following the steps shown in the previous section in this tutorial.

All merged cells are selected

Once you know which cells are merged, it becomes easier to unmerge the cells that you really need to unmerge.

Merged cells are unmerged

Unmerge all Cells in Excel Using VBA

Another way to unmerge all cells in Excel is by using a VBA script. Here’s the code that you can use:

Sub Unmerge_all_merged_cells()
For Each Cell In ActiveSheet.UsedRange
If Cell.MergeCells Then
Cell.Style = “Note”
Cell.UnMerge
End If
Next
End Sub

This code loops over each cell in the working area of your active sheet. If it detects a merged cell, it simply unmerges it, as specified by the line:

Cell.UnMerge

To run the above code follow the steps shown below:

  1. Make sure you have the sheet you want to work on as the active sheet.
  2. From the Developer menu, select Visual Basic.     
  3. Once your VBA window opens, Click Insert->Module. Now you can put your code in. Copy and paste the above code into the module window.

Run the code by pressing F5 on the keyboard or clicking on the Run button () from the toolbar.

Here’s the result that you should get:

Dataset with unmerged cells

Unmerge All Cells and Copy the Original Values to Unmerged Cells

Usually unmerging the cells will end up displaying the contents of the original (merged) cell at the top- or left-most cell of the unmerged group, as shown below:

Before unmerging

Dataset with merged cells

 After unmerging

Dataset after unmerging

If you prefer to have the original values copied to all the cells that were part of the merger, then you can do so by the following steps:

  1. Select all the cells and unmerge as shown in the first section of this tutorial.
  2. Select the main active area (that contains the actual contents of your table).
  3. From the Home tab, under the Editing group, click on the dropdown arrow next to Find & Select.
Click on Find and Select
  1. Select ‘Go to Special…’ from the dropdown list that appears.
Click on Go To Special
  1. This will open the Go to Special dialog box. Check the radio button next to the ‘Blanks’ option and click OK.
Click on Blanks
  1. This will select all the blank cells (that were created by the unmerge action).
All blank cells are selected
  1. Type the equal sign (=). If all the cells were merged vertically, press the Up arrow key on your keyboard. This will copy the value of the cell which is directly above the first blank cell. If all the cells were merged horizontally, however, press the Left arrow key on your keyboard. This will copy the value of the cell which is directly to the left of the first blank cell, as shown below:
Enter the reference of the cell above
  1. Press CTRL+Enter to copy the formula to all the cells which became blank after unmerging.
Value from cell above is copied

You should now find each blank cell filled with the values from their previously merged cell.

Note: The blank cells now contain formulae that depend on other cells. If you want to replace these formula results with the actual resultant value, then press CTRL+C to copy, then right-click and select Paste Special->Values.

In this tutorial, we showed you two ways to unmerge all cells in Excel.

The first method involves the use of Excel’s ‘Merge and Center’ feature, while the second method involves the use of a simple VBA script.

We also showed you how you can have more control over the cells you unmerge.

Finally, we provided a small bonus section on what to do if you want to fill all the unmerged cells with a copy of their original values.

I hope you found this tutorial useful!

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “How to Unmerge All Cells in Excel?”

  1. Good day,
    it seems that nearly always the boxes (e.g. Dataset with merged cells) are not working properly.
    It seems, as well, that the possible same hiccup happens in the two previous tutorial.
    Many thanks for your time and coutesy.
    Ciao.

    Reply

Leave a Comment