Let’s say you’re working with a team schedule in Excel and need to update employee shifts. Some cells are merged to show shared shifts, and you need to find these merged cells to make changes without changing the layout.
In that case, you must find the merged cells in excel to and unmerge them, and finding theses cells one by one could be a time-consuming task.
But, 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
This is one of the best ways to find all the merged cells in one go from the active worksheet.
- 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.
Find Merged Cells in Excel Online
Excel Online has limited functionality compared to the desktop version, and you need to do it manually to find merged cells. Adding borders to all cells is a practical workaround. This way, merged cells will be clearly visible as larger than normal cells with a single border.
To add borders, select the range, go to the “Home” tab, click on the “Borders” in the “Font” group, and choose “All Borders.”
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.
How Merged Cells in Excel Create Problems
Merged cells in Excel can cause a few problems. First, they can make sorting and filtering your data difficult because they disrupt the layout of rows and columns.
Also, if you use formulas referencing a range of cells, merged cells can lead to incorrect calculations because they’re treated as a single cell.
To avoid these issues, always try to use a minimum number of merged cells in your worksheet. Or you can unmerge cells back to normal if you don’t require them anymore.
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 want to unmerge multiple merged cells, you can select them all.
- Go to the Home Tab: At the top of Excel Ribbon click the “Home” tab to open it. Bu default, when you open Excel, Home Tab is already open.
- Find the Merge & Center Button: In the “Alignment” group on the Home tab, you will find the “Merge & Center” button.
- Click on the “Merge & Center” Dropdown: Click on the dropdown arrow next to the “Merge & Center” button for more options.
- Select “Unmerge Cells”: From the dropdown menu, select “Unmerge Cells.” This will unmerge any selected merged cells.