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:
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:
- 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:
- From the Home tab, under the Alignment group, click on the dropdown arrow next to Merge & Center.
- Select Unmerge Cells from the dropdown list that appears.
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:
- Select all the cells by pressing CTRL+A on the keyboard or clicking on the Select All Cells button.
- From the Home tab, under the Editing group, click on the ‘Find and Select’ dropdown.
- Select Replace from the dropdown list that appears.
- This will open the Find and Replace dialog box.
- Select Options. This will display more Find and Replace options.
- Click on the Format button next to the ‘Find What’ field. This will open the ‘Find Format’ dialog box.
- Select the Alignment tab and make sure the box next to ‘Merge Cells’ is checked.
- Click OK.
- You should be back to the Find and Replace dialog box now. In this box, click ’Find All’.
- You can now see a list of all the merged cells in the worksheet.
- You can choose to select all these merged cells by pressing CTRL+A or select only the cells that you want to unmerge.
- 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.
Once you know which cells are merged, it becomes easier to unmerge the cells that you really need to unmerge.
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:
- Make sure you have the sheet you want to work on as the active sheet.
- From the Developer menu, select Visual Basic.
- 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:
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
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:
- Select all the cells and unmerge as shown in the first section of this tutorial.
- Select the main active area (that contains the actual contents of your table).
- From the Home tab, under the Editing group, click on the dropdown arrow next to Find & Select.
- Select ‘Go to Special…’ from the dropdown list that appears.
- This will open the Go to Special dialog box. Check the radio button next to the ‘Blanks’ option and click OK.
- This will select all the blank cells (that were created by the unmerge action).
- 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:
- Press CTRL+Enter to copy the formula to all the cells which became blank after unmerging.
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:
