Search | Merged cells

Top  Previous  Next


This command selects all the merged cells which intersect the current selection.

Why would you want to use it?

There are many practical reasons to dislike merged cells.

Highlight a rectangular range of cells that includes some merged cells, and the selection will automatically be enlarged so that it includes the merged area.  It is impossible to select a smaller area.

You can select an entire column or row by clicking on its header.  But if the column or row intersects any merged cells, those cells get selected too.  That can be tiresome; in particular, it stops the Insert rows and Insert columns commands from working (see Chapter 10 for a way around this).

You can select an entire column or row with the keyboard: Ctrl+Space selects the column, Shift+Space the row.  But if the column or row intersects any merged cells, those cells get selected too, along with the entire columns or rows that they in turn intersect.  In most circumstances that renders the ability to select rows and columns with the keyboard unusable.  This is particularly confusing if the merged cells happen to be hidden.

Text that won't fit in a cell is usually able to spill out into its neighbors, so long as they are unoccupied.  In a merged cell, however, the text is truncated.

Unlike every other cell formatting option, the merging of cells is not simply a characteristic of a format style; it actually changes the regular grid structure of your spreadsheet.

A common use of the Excel Merge and Center command is to center heading text above a number of columns over which the heading applies.  The OAK  | Search | Merged cells command allows you to find any merged cells that exist on your worksheet. Once identified, the merged cells can then be reverted back into individual cells and formatted as follows to achieve the same appearance of text within merged cells:

open the Format Cells dialog box either by right-clicking the mouse on an active merged cell or by using Ctrl+O,E;

select the Alignment Tab;

change the Horizontal Text alignment option to “Center Across Selection”;

uncheck the Merge cells option under Text control; and,

click OK to close the dialog box.

How to use it

Either select the range of cells you suspect to contain merged cells or select just a single cell to find all the merged cells on the worksheet.  Then choose the option 'Merged cells' from the Search pane; it will either select those merged cells that intersect the current selection or return a “No merged cells found in selection” message.


If the active cell happens to be a merged cell, then the OAK | Search | Merged cells command will only identify this cell, ignoring other merged cells within the worksheet.  In this case, the One-Cell Rule does not apply because the active cell is being treated as a selection of more than one item as opposed to a single cell.

What you could do if you didn't have OAK

There are no comparable tools within Excel capable of locating multiple merged cells in a worksheet.  A probable solution would be to implement a search algorithm in a macro.  The identified merged cells can then be split apart using the “Center Across Selection” Horizontal Text alignment formatting option described above.