Merging and unmerging data is a common thing to do in Excel. Well, it often becomes problematic when you need to sort data in Excel while having already merged cells in it. Suppose, you have a data range that has merged cells and you need to sort it, you will get an error:
“This operation requires merged cells to be identically sized.”
In this case, you must be thinking about how to find a merged cell in Excel so that you can easily manage to sort data without trouble. If you ever noticed, you must realize that merged cells are often presented so nicely that you will never find them easily. And when you have a larger dataset, it becomes even trickier. When you have merged cells in the dataset, Excel will not let you perform the following tasks:
- Data sorting is not allowed in the presence of merged cells.
- Using references to merged cells in formulas is not allowed.
- Copying a cell range that contains merged cells is not allowed.
- Counting cells in the presence of merged cells is not allowed.
- You may often find difficulties in the VBA code just because of merged cells.
Let’s see how to find a merged cell in Excel by using multiple methods:
Use Find n Replace to Find Merged Cells in Excel:
Find and Replace is a superb feature used to find something from the dataset you have. With its great abilities, you can even find merged cells in an Excel sheet. Let’s understand the procedure by following the steps given below:
In the above dataset, you can see C1 and D1 both cells are being merged. Apart from that cell A5 and A6 are also merged. Suppose you need to find them in the sheet with the help of Find and Replace. For this, have a look at these steps:
- Choose a cell range that has merged cells.
- Open the Home tab and click on the Find and Select option given under the Editing menu.
- Choose Replace from the dropdown menu or else you can press the CTRL + H keys to open the Find and Replace menu.
- Click on the Options button from the Find and Replace box.
- Choose the Format option and you will see a dialog box Find Format.
- Click on the Alignment button and check the Merge Cells option given under the Text Control menu.
- Press OK. The find and Replace dialog box will pop up.
- Choose the Find All option and a list of merged cells will appear in the selected range.
- Select the first list item and it directly selects all the cells in the list. Now, press down the button on the SHIFT key and choose the last item. In this way, all your list items will get selected instantly.
- Click on the Close button and the Find and Replace dialog box will be closed.
That’s it!
How to Unmerge Cells in Excel on Windows?
In the above section, you have come to know how to find all the merged cells present in a sheet. Now, let’s see how you can unmerge these cells easily because you often need to remove these merged cells. Let’s see how you can do it:
- First, you need to find all the merged cells in a sheet. Now, as you already have found all of these cells, select them.
- Click on the first result by holding the SHIFT key and clicking the last result to select all of these cells.
- Open the Home tab and click on the down arrow that appears next to the Merged & Center option.
- Choose Unmerge Cells option.
- Now, all of the cells are unmerged.
That’s it!
Using VB Script to Find Merged Cells in Excel:
If you are a code lover, the VB script is fondly used to help you in finding as well as unmerge all the merged cells present in a sheet.
So, here we go:
Sub findMergedCells()
ForEach Cell In ActiveSheet.UsedRange
If Cell.MergeCells Then
Cell.Style = “Note”
EndIf
Next
End Sub
Ensure the sheet is there as an active sheet in which you need to work. And the above code will by default run through each cell in the sheet you have opened.
While running through each cell, when a merged cell is found you will see the MergeCells option will appear as True and it shows the cell by setting its Style option to Note.
Let’s see how you can use the above code:
- Make sure the sheet opened in front of you in which you need to work.
- Choose the Visual Basic option given in the Developer Menu group.
- Select Insert and then the Module option once the VBA screen pops up. You can now put the above code in this VBA screen.
- Simply Copy and Paste the code.
All set!
Summary:
In this post, you have come to know how to find a merged cell in Excel. Not only this, but you have learned how you can unmerge these cells by using VBScript coding. Practice all of the above methods to learn them.
