After you merge cells, they no longer behave like normal cells and often result in issues when sorting or using inside a formula. So, it’s better to avoid using them in the first place.
Even if your worksheet already contains such cells, it’s quite easy to identify them as they are larger than normal cells. But, manually looking for such cells in a large dataset can be a tedious job.
Instead, you can use tools like the Find and Replace feature and VBA to quickly find and select all of them at once.
Using Find and Replace
Along with finding text/number characters, the Find and Replace can also search for cells with specific formatting like merged cells. Once you find them, selecting is just a piece of cake.
- Select all the cells that may contain merged cells. You can use the shortcut key Ctrl + A for this.
- Then, press Ctrl + H.
- On the Find and Replace prompt, click Format in the Find what field.
- Next, select the Alignment tab.
- Under the Text control section, uncheck all the options except the Merge cells checkbox.
- Click OK.
- Click Find Next to go through each merged cell individually.
- To select all the merged cells, click Find All.
- Then, select the first match and click the last one while pressing the Shift button.
- Click Close.
If the above steps didn’t select the merged cells, they are probably using the Center across selection formatting, which makes cells appear merged. To find such cells, follow the same steps as mentioned above. But, this time, replace steps 5 and 6 with the two steps below.
- Repeat the same four steps as mentioned above.
- Select the Center Across Selection option under the Horizontal section. Also, uncheck options under the Text control section.
Using VBA Code
Another way to find the merged cells is by executing a VBA code on your worksheet. Once the code selects all the merged cells, you can unmerge them all at once.
- Open the worksheet where you want to find merged cells.
- Right-click the worksheet and select View code.
- On the new window, copy and paste the code mentioned below.
Sub SelectAllMergedCells() Dim c As Range Dim mergedCells As Range Dim fullRange As Range Dim rangeDescription As String If Selection.Cells.Count > 1 Then Set fullRange = Selection rangeDescription = "selected cells" Else Set fullRange = ActiveSheet.UsedRange rangeDescription = "active range" End If For Each c In fullRange If c.MergeCells = True Then If mergedCells Is Nothing Then Set mergedCells = c Else Set mergedCells = Union(mergedCells, c) End If End If Next If Not mergedCells Is Nothing Then mergedCells.Select Else MsgBox "There are no merged cells in the " _ & rangeDescription & ": " & fullRange.Address End If End Sub
- Press F5 or click the Run button below the menu bar.
- Once Excel finishes executing the code, the merged cells are selected like in the image below.
- To unmerge such cells, click the Merge & Center button under the Home tab.