Many folks use merged cells in their worksheets and they can definitely be helpful, but many times merged cells can cause you untold heartburn when selecting ranges, sorting and other functions we use in Excel every day.
Even if you do not use merged cells because you once experienced the problems mentioned above, you certainly may find yourself working on a worksheet that was created by someone else which contains merged cells.
If you want to be certain there are no merged cells in your worksheet follow the steps below to get rid of them.
- Click CTRL + F to open the Find and Replace dialog box.
- Be certain the Find what field is clear and the Match case and Match entire cell contents have not been selected.
- Click on Format.
- Select the Alignment tab.
- Check the Merge cells option.
- Click OK to close the dialog.
- Click Find All. Note that all merged cells will 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 and you will notice that all of those cells will also be selected in your worksheet as well.
Now that you have identified the merged cells, you can:
- Close the Find dialog and apply a color background to the selected cells so that they stand out for you and you can locate them easily.
- Keep the Find dialog open and do your editing in the worksheet and when you need to select a group of merged cells, you can click the cell reference in the Find dialog. If you have un-merged some cells, you can click the Find All button once again to update your list.
Be certain once you have used this process, that you clear the formatting from the Find what criteria by clicking Format | clear | Ok in the Find dialog to prevent a problem when you next use the Find dialog.
—
