Most folks will tell you that Merged cells are a pain in the butt! Why? Because they can cause you lots of problems when you are selecting ranges, pasting, sorting, and other functions.
Perhaps you may be in the habit of not using merged cells in your worksheet, but have fallen prey to them in a colleague’s worksheet that you are collaborating on.
Why do I say “fallen prey to them?” Well it is because merged cells are not easily found in a worksheet. Depending upon how your worksheet is formatted, merged cells can be indistinguishable from other cells.
If you want to remove all merged cells from your worksheet, you may have your work cut out for you because finding all of them can be quite a challenge.
I have recently learned that there is a simple method for finding these merged cells and I will now share it with you. But, please, remember to always make another copy of your workbook in case something should go awry when you are trying something new!
Follow the steps below to learn how to easily find the merged cells in your worksheet:
- Click CTRL + H to display the Find and Replace dialog box.
- Be certain that the Find what field is empty and the Match case and Match entire cell contents options are not selected.
- Click the Format button.
- Click the Alignment tab.
- Select the Merge cells option.
- Click OK to close the Find Format dialog.
- Click the Find All button.
All merged cells will now be listed at the bottom of the dialog.
With the Find dialog box still open, click CTRL + A to select all cells listed at the bottom of the dialog and you will see that all of those cells will be selected on your worksheet as well.
You now have two options: You can then either:
- Close the Find dialog box and apply a background color to the selected cells to make them more visible; or
- Keep the Find dialog box open and do your editing on your worksheet. When you need to select another group of merged cells, you can click the cell reference in the Find dialog box. If you have some of your cells that you have cleared the merge on, you can always click the Find button once again to update your list.
