Many times, you get data that has merged cells which stops you to perform some activities like data sorting, using the reference to the merged cells in formulas, and using the filter selection properly.
In that case, you must find the merged cells in excel to unmerge and adjust them, and finding the merged cells one by one could be a time-consuming task.
But, just sit and relax, Excel has the functionality to find and highlight all the merged cells in one go to overview them quickly before unmerging them and we have described that functionality for you.
Steps to Find Merged Cells Using Find and Replace
- First, select the cell range or the entire sheet from where you want to find the merged cells.
- Once the range got selected, go to the “Home” tab and under the “Editing” group click on the “Find & Replace” icon and select Replace option to open the Replace dialog box.
- Or simply press the “Ctrl + H” shortcut key to open the “Replace” dialog box.
- After that, click on the “Format” option in the “Find and Replace” dialog box and you will get the “Find Format” dialog box opened.
- In the “Find Format” dialog box, go to the “Alignment” tab and tick mark the “Merge cells” option and untick the “Wrap text” and “Shrink to fit” options properly as shown in the image, and click OK.
- Now, click on “Find All” in the “Find and Replace” dialog box and you will get all the merged cells listed below in the dialog box.
- Now, click on any listed cell in the “Find and Replace” dialog box and select all the listed cells using the “Ctrl +A” keys and your merged cells in the data will get selected.
- To highlight the selected merged cells, go to the “Home” tab and under the “Font” group, click on the “Fill Color” icon and select the color, and your merged cells will get highlighted and you can overview them quickly before unmerging them.
- In the end, to unmerge them, just click on the “Merge & Centre” icon under the “Alignment” group and all the merged cells will get unmerged.
In Excel Find Merged Cells with a VBA Code
Users can also use a VBA code to find and highlight the merged cells.
Sub find_merged_cells()
Dim Cel As Range, Msg As String, Addr As String
For Each Cel In ActiveSheet.UsedRange
If Cel.MergeCells Then
Cel.Interior.Color = 65535
End If
Next
End Sub- Dim Cel As Range, Msg As String, Addr As String: This line is declaring three variables. Cel is declared as a Range object, which will be used to refer to a cell. Msg and Addr are declared as String but are not used in this macro.
- For Each Cel In ActiveSheet.UsedRange: This line starts a For Each loop that will iterate through each cell in the currently active sheet in Excel. ActiveSheet.UsedRange refers to the area that contains data or formatting.
- If Cel.MergeCells Then: This line is an If statement that checks if the current cell (Cel) is part of a merged cell. MergeCells is a property that returns True if the range contains merged cells, and False otherwise.
- Cel.Interior.Color = 65535: If the current cell is a merged cell, this line changes the interior color of the cell to yellow. In VBA, colors are represented by a unique number, and the number 65535 represents yellow.
- End If: This line marks the end of the If statement.
- Next: This line marks the end of the For Each loop. After executing the commands in the loop for the current cell, it moves on to the next cell in the ActiveSheet.UsedRange.
- End Sub: This line marks the end of the macro. After this line is executed, the macro stops running.
Unmerging Cells in Excel Once you Find them
Unmerging cells in Excel that have previously been merged can help you avoid some of the issues associated with merged cells, such as sorting and filtering difficulties, formula errors, and data entry.
- Select the Merged Cell: Click on the all the cells you want to unmerge. If you unmerge multiple merged cells, you can select them all by clicking and dragging your cursor across them.
- Go to the Home Tab: Look at the ribbon at the top of Excel and click the “Home” tab to view the Home toolbar.
- Find the Merge & Center Button: In the “Alignment” group on the Home tab, you will find the “Merge & Center” button. This button is used both for merging and unmerging cells.
- Click on the “Merge & Center” Dropdown: Click on the small dropdown arrow next to the “Merge & Center” button to reveal more options.
- Select “Unmerge Cells”: From the dropdown menu, select “Unmerge Cells.” This will instantly unmerge any selected merged cells.