How to Find Merged Cells in Excel (2 Simple Ways)

You might have encountered times where you find Excel not letting you perform certain operations on your data due to the presence of merged cells.

Merged cells are often so subtle in appearance that it’s difficult to hunt them down one by one. It becomes even tougher when you are dealing with large datasets where there can be multiple instances of merged cells.

Here are some cases in which Excel does not allow you to perform operation due to the presence of merged cells:

When you try to sort cells that contain merged cells.
When you need to use references to merged cells in formulas.
When you need to copy a range of cells that may contain merged cells.
When you need to count cells that contain merged cells.
Sometimes merged cells may cause disturbances in your VBA code too.

In this Excel tutorial, we will show you two quick and easy ways in which you can use to find merged cells in your worksheet, even if it is a large one.

Once you have found the merged cells, you can further process them by either unmerging them, highlighting them, or removing them.

Table of Contents

Method 1: Finding Merged Cells Using ‘Find and Replace’

The Find and Replace feature provides a great way to select merged cells in Excel.

Let’s say you have the dataset shown below:

In this dataset, cell C1 has been merged with cell D1. Moreover, cell A5 has been merged with cell A6.

Now to find these merged cells, you can use Find and Replace as follows:

Select the range of cells in which you want to find merged cells.
From the Home tab, under the ‘Editing’ group, select ‘Find and Select’.
Select Replace from the dropdown. Alternatively, you can press the CTRL+H keys on your keyboard to directly open the ‘Find and Replace’ dialog box.
In the ‘Find and Replace’ dialog box, select Options.
Now click on Format (next to the ‘Find What’ input field). This will open the ‘Find Format’ dialog box.
Select the Alignment tab and from the Text Control options, check the “Merge Cells” option.
Click OK. This will bring you back to the ‘Find and Replace’ dialog box.
Click the ‘Find All’ button. You will see a list of all the cells that are merged in your selected range.
Select all these listed cells by selecting the first list item, and pressing down on the SHIFT key, select the last item. This will cause all your merged cells to get selected simultaneously.
Press the Close button to close the ‘Find and Replace’ dialog box.

The above steps would instantly select all the cells that are merged in the dataset

Highlighting all Merged Cells in your Worksheet

The above steps will help automatically select all the merged cells in your sheet. You can now choose to highlight all the merged cells so that you can decide what to do with the cells.

For this, select one of the styles from the Styles group under the Home tab (or choose any other cell color in which you want to highlight the merged cells).

Now you can clearly see which cells are merged.

At this point, it’s up to you to decide if you want to delete these cells, replace them or simply unmerge them.

Unmerging all Merged Cells in your Worksheet

Once you have found all the merged cells in your sheet, you can decide to unmerge these cells so that they become easier to work with.

For this Go to the Home tab and click on the ‘Merge & Center’ button. This will instantly unmerge all the cells.

Now you can sort the data as it would not have any merged cells.

Also read: Excel Autofit Row Height Not Working - How to Fix?

Method 2: Using VBScript to find and highlight all Merged Cells in your Worksheet

If you prefer to use VBScript to programmatically find and highlight/unmerge all the merged cells in your sheet, here is a super-easy way.

Here’s the code that we will be using:

Sub findMergedCells()
ForEach Cell In ActiveSheet.UsedRange
If Cell.MergeCells Then
Cell.Style = "Note"
EndIf
Next
End Sub

Make sure you have the sheet you want to work on as the active sheet. The above code will cycle through each cell in your active sheet’s working area.

Wherever it finds the MergeCells property set to True, it highlights the cell by setting its Style property to “Note”.

Below are the steps to use the above VBA macro code:

Make sure you have the sheet you want to work on as the active sheet.
From the Developer Menu Ribbon, 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

Using VBScript to Find and Unmerge All Merged Cells in your Worksheet

If you want to unmerge all the merged cells in your sheet, then you can add an extra line after line 5:

Cell.UnMerge

So your code will be:

Sub unmerge_mergedcells()
ForEach Cell In ActiveSheet.UsedRange
If Cell.MergeCells Then
Cell.Style = "Note"
Cell.UnMerge
EndIf
Next
End Sub

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

Which of the Two Methods is Better?

Among the two methods shown above, you can choose to follow any method you are comfortable with.

If you are not experienced with or are not comfortable with coding, the first method would be easier for you. However, if you use the second method, you can get the work done quickly since you can build the code into a macro and find all your merged cells with a single click every time.

The first method requires you to go through a series of steps, which is alright if you have a small number of merged cells.

However, if you need to find a large number of merged cells in large datasets or you need to do the same for multiple workbooks, the second method will save you loads of time.

Use Center Across Selection to Avoid Merging Cells

As a rule of thumb, it is usually advised to avoid merging cells. Instead, you can use ‘Center Across Selection’. This makes your cells look like they’re merged, but don’t require the actual underlying cells to merge together. It’s really easy to do this.

If you want to span your text horizontally over two columns, select the two cells, right-click on the selection and select Format Cells from the popup menu.

From the Format Cells dialog box, select the Alignment tab and under Text Alignment, select ‘Center Across Selection’ from the Horizontal options.

This way, you don’t have to worry about all the issues related to operating on ranges that contain merged cells.

Conclusion

In this tutorial, we showed you two ways to find merged cells in Excel. Among these, the first method is the manual method that involves going through a series of steps to find all the merged cells.

The second method involves using a few lines of code to find all the merged cells by the click of a button. We also showed you what you can do once you find all the merged cells.

For example, you can either highlight the cells to see them more clearly or unmerge them. In the end, we also discussed some good practices when using Excel worksheets so as to avoid the problems associated with merged cells.

Hope you found this Excel tutorial helpful!

Other Excel tutorials you may like:

How to Find Merged Cells in Excel?