Merge and Unmerge Cells
Three different jobs get called merging cells, and only one of them is what the Merge button does. Making one wide cell out of several — a banner over a group of columns — is the Merge & Center job, and only the value in the upper-left corner survives it. Putting what two cells say into a single value is not a merge at all but a formula. Undoing somebody else's merged layout is a third task again, with its own hunt-and-repair sequence, because unmerging returns the cells but never the values that were thrown away.
The formula
one wide cell for a heading Merge & Center — or Center Across Selection for the same look
two values in one cell a formula: =A2&" "&B2 or TEXTJOIN; merging cannot do this
undo an inherited merged mess find them by format, unmerge, then refill the blanks left behind
find every merged cell Ctrl+F > Options > Format > Alignment > Merge cells > Find All
refill after unmerging Ctrl+G > Special > Blanks, type = and the cell above, Ctrl+EnterA worked example
An inherited workbook has merged cells scattered through a 900-row data range, and sorting it now fails.
Ctrl+F, click Options, Format > Alignment, tick Merge cells, clear the Find what box, and choose Find All.
Every merged cell on the sheet is listed and clicking a result jumps to it. Unmerging them restores the grid but leaves the freed cells empty, because the merge kept only the top-left value in each block — so follow with Ctrl+G > Special > Blanks, type = and a reference to the cell above, and press Ctrl+Enter to fill the column back down before sorting.
Which one do I need?
| If you want to… | Use |
|---|---|
| A title centred across several columns | Ctrl+1 > Alignment > Horizontal > Center Across Selection — the same appearance with none of the consequences |
| Two cells' contents combined into one value | A formula or Flash Fill. The Merge button joins cells and never their contents |
| Clearing merged cells out of an inherited sheet | Find them by format with Ctrl+F > Options > Format > Alignment, unmerge, then refill the blanks with Go To Special |
| Sorting or filtering has stopped working on the range | A merged cell is inside it. There is no override — the merge has to go |
| The text in a merged cell is cut off | Merged cells never grow their own row height, so set the height by hand rather than relying on wrapping |
| AutoFit Row Height does nothing on that row | AutoFit skips merged cells entirely — the same limitation seen from the other side |
| The range is an Excel table | Tables refuse merged cells outright, so the layout and the table are mutually exclusive |
Frequently asked questions
How do I find every merged cell in a sheet?
Press Ctrl+F, click Options, then Format > Alignment, tick "Merge cells", leave the "Find what" box empty and choose Find All. Every merged cell on the sheet is listed and clicking a result jumps straight to it. Excel searches one sheet at a time by default — switch Within to Workbook in the same dialog to sweep the whole file at once.
After unmerging, why is everything blank except the first cell?
Because the merge discarded the other values when it was applied, sometimes years earlier — unmerging returns the cells but has nothing to put back into them. Rebuild the column with Ctrl+G > Special > Blanks, then type = and a reference to the cell immediately above the active blank and press Ctrl+Enter, which fills every selected blank with the value above it. Paste Special > Values afterwards to make them static.
Does Google Sheets have Center Across Selection?
No, and that changes the advice there. Google Sheets offers Format > Merge cells with Merge all, Merge horizontally and Merge vertically, all of which keep only the top-left value exactly as Excel does, but there is no alignment option that fakes the look without joining the cells. A centred banner in Sheets genuinely has to be a merge, so keep it out of any range you intend to sort.
Can I merge cells inside an Excel table?
No. Merge & Center is greyed out for any selection inside a table, because a table guarantees one value per cell per row and a merge breaks that promise. Either send the table back to being a range from Table Design and accept losing the structured references, or leave the table alone and use Center Across Selection on a heading row above it.
New guides and tools, once a month
DE + EN · double opt-in · no spam