How To Find Merged Cells in Excel (2 Simple Ways)Explained with Examples
Chances are, if you have used Excel regularly, you have come across occasions where the program will not let you complete certain operations because of merged cells.
Unfortunately, hunting down merged cells manually is often extremely difficult and time-consuming.
It may even be nearly impossible in large datasets when there are several instances of merged cells that can be visually near identical to ordinary cells.
In this article, we will show you two easy ways you can find merged cells in your worksheets.
What Operations Are Blocked in Excel Due to Merged Cells
Excel will block a number of operations from being performed if there are merged cells.
These include:
- When attempting to sort cells that include merged cells.
- If formulas require a reference to merged cells.
- When you want to copy cells that include merged cells.
- If you attempt to copy a range of cells, including merged cells.
Merged cells can also cause issues when using VBA code as well.
When any of these issues arise, the only solution is often to remove the merged cells.
Locating Merged Cells with Find and Replace
One of the easiest ways to locate merged cells is using Excel’s Find and Replace feature.
For example, consider this dataset below.
Cells B5 and B6 of this data set have been merged as well as cells E1 and F1.
In order to find the merged cells, one option you have is the Find and Replace tool.
In order to use this tool, choose the range of cells you would like to search for merged cells in.
Next, under the “Home” tab within the “Editing” group, select the “Find and Select” button.
In the drop-down list, select “Replace.”
If you prefer using keyboard commands, you can also press “Ctrl + H” in order to directly bring up the “Find and Replace” window.
Within this window, click on the “Options” button and then the “Format” button.
This will bring up the “Find Format” window.
Here under the “Text Control” options, select the “Merge Cells” box and click OK.
You will be returned to the “Find and Replace” window, where you will now select the “Find All” button.
This will bring you to a list consisting of all merged cells within the range you have selected.
You can select all of the merged cells by clicking on the first item on the list and then holding down your Shift key, and clicking on the final item on the list.
This will simultaneously select every list item allowing you to highlight or unmerge as you choose.
Now you can select “Close” in order to exit the “Find and Replace” window.
Highlighting Merged Cells
Now that you have selected all of the merged cells in the worksheet using the above steps, you may want to highlight them.
This will make it easier for you to identify and choose what you will do with them next.
In order to do this, click on home and look under styles.
Then, choose one of the styles.
This will change the appearance of the cells allowing you to easily identify the merged tabs.
After doing this, you can choose to delete, replace, or unmerge them.
How To Unmerge Merged Cells
With your merged cells identified now, you can choose to unmerge them.
This will allow them to be much more easily worked with.
To do this, click on home.
Then, select “Merge and Center.”
On the drop-down list, select “Unmerge Cells.”
This will unmerge the merged cells allowing you to proceed without worrying about navigating around merged cells.
How To Find Merged Cells Using VBA Codes
If you feel more comfortable using VBA codes to find and remove the merged cells in a worksheet, it is easy to do it this way as well.
Start by pressing the Alt + F11 buttons on your keyboard to bring up a window for the Visual Basic editor.
For Mac users, the shortcut is to use Opt + F11 or Fn + Opt + F11. Next, right-click on the VBA project area and move your cursor over the “Insert” option.
Within the sub-menu, select the option “Module” in order to insert a module for your current workbook.
Here is the code you can input to identify and highlight any merged cells.
Sub findMergedCells()
ForEach Cell In ActiveSheet.UsedRange
If Cell.MergeCells Then
Cell.Style = “Note”
EndIf
Next
End Sub
When you use this code, be sure that the sheet on which you are working is the active sheet.
As long as it is, the code you just entered will work through each of the cells in the working area of your active sheet.
In any cell where it finds that the “MergeCells” is “True,” it will change the style highlighting the cell.
How To Unmerge Cells Using VBA Codes
If you would like to unmerge the merged cells within a worksheet, this is easy to do with VBA codes as well. Just add one more line to the above code.
The code you will add is:
Cell.UnMerge
Simply add this code and click the button on the toolbar to run the code and unmerge the merged cells in your worksheet.
Which Method Should You Use?
The fact is that either of the methods described above will work and are easy to do.
However, if you aren’t familiar with using VBScript or coding in general, then the find-and-replace method is likely to be easier.
On the other hand, if using VBA codes is nothing new for you, then this code is easy and quick and can be applied to other workbooks simply by changing the target range.
This will avoid having to repeat the several steps involved in using the “Find and Replace” feature each time you need to find or unmerge cells in your workbook.
In smaller datasets or when you do not often need to work with Excel, the find-and-replace method is great.
Regardless of which you prefer, if you find you have a lot of merged cells to identify, either method will work in a pinch.
How To Avoid Merging Cells
Generally, merging cells in Excel should be avoided in order to prevent confusion and difficulty performing working with data down the line.
If you would like to make your cells appear merged without the problems associated with merging them, you can try “Center Across Selection” instead.
In order to horizontally align text over two columns, simply select both cells and right-click the selection.
Next select “Format Cells“ in the drop-down list. In the dialog box that appears, select the tab marked “Alignment.”
In the “Text Alignment” section, find and select “Center Across Selection” in the “Horizontal” options.
Click OK, and this should immediately format your cells.
Conclusion
Now you have seen two methods for identifying and locating merged cells in an Excel worksheet.
The first of these methods can be performed manually to find the merged cells in only a few steps.
The VBA codes method simply involves entering a few lines of code in order to immediately locate all of the merged cells.
Further, you have seen how to easily highlight or unmerge these cells once they are located.
You have also seen a powerful method for avoiding the need to use merged cells in the future.
Now, with this knowledge in your tool belt, merged cells do not ever have to give you trouble again.