How To Locate All Merged Cells On A Worksheet
by Francis Hayes (The Excel Addict)
Merging cells in a worksheet can sometimes be helpful but, more often than not, they can cause you plenty of grief when it comes to selecting ranges, using Paste commands, sorting, filtering and many other everyday functions.
Hopefully you have developed a habit of NOT using merged cells in your worksheets. But chances are, you will sometimes find yourself working with worksheets that someone else has created.
Merged cells are not always easy to locate in a worksheet. Depending on how the worksheet is formatted, merged cells are often indistinguishable from other cells.
If you want to 'exorcise' all merged cells from a worksheet, finding them all could be quite a challenge. So here's a simple technique I use for locating all merged cells in a worksheet.
CAUTION: You should always make a backup copy of any workbook before making major changes.
1) Press CTRL+F to open the Find and Replace dialog;
2) Make sure that the 'Find what' field is empty and the 'Match case' and 'Match entire cell contents' options are not checked;
3) Click the Format... button;
4) Select the Alignment tab;
5) Clear the 'Wrap text' and 'Shrink to fit' options** and check the 'Merge cells' option;
6) Click OK to close the Find Format dialog;
7) At the bottom of the Find and Replace dialog, click the Find All button. All merged cells will be shown at the bottom of the dialog;
8) With the dialog still open, press CTRL+A and all of the cells listed at the bottom of the dialog will be highlighted. Also notice that all of those cells are selected in the worksheet as well;
At this point you can either:
(a) Unmerge all of the cells in one step by clicking the small arrow on the right of the Merge & Center command (on the Home tab), then clicking Unmerge Cells;
(b) Close the Find and Replace dialog and apply a background/fill color to the selected cells to make them easier to identify for working with later, or...
(c) Keep the Find and Replace dialog open, drag it's header to move it out of the way and editing the cells in the worksheet. To select another group of merged cells in the worksheet, click a cell reference from the list at the bottom of the Find and Replace dialog. If you have unmerged some cells, you may want to click the Find All button again to update the list.
CAUTION: If you insert or delete rows or columns in the worksheet, some of your merged cells may be moved to different locations. The cell references in the Find and Replace dialog won't automatically update, so you will need to do the Find All / CTRL+A steps again.
Anytime you use 'Find and Replace' for finding formats, I recommend that you clear the formatting from the 'Find what' criteria by clicking the small arrow on the right side of the Format... button, then click Clear Find Format, OK. This could help avoid problems the next time you need to use the Find and Replace dialog.
You can tell that a Find Format has been set if you see 'Preview*' displayed to the left of the Format button. When the format has been cleared, it will say 'No Format Set'.
** If you find that this doesn't find some merged cells in your worksheet, it may be that the merged cells are also formatted as 'Wrap text' and/or 'Shrink to fit'. If that's the case, you will need to check one or both of these options, in addition to the 'Merge cells' option in the Find Format dialog.
To get more tips like this one every week...
'Spreadsheet Tips From An Excel Addict'
'Excel in Seconds' & 'Excel in Minutes'
And I'll give you my 'Excel in Seconds' E-book as a BONUS!
(Download it immediately after you sign up)
Copyright Francis Hayes © All Rights Reserved
8 Lexington Place, Conception Bay South, NL Canada A1X 6A2
Phone 709-834-4630
This site is not affiliated with Microsoft Corporation.