Established onExcel 365 and 2016 and later: Center Across SelectionGoogle Sheets, web: no equivalent alignment optionBoth: table conversion refuses merged ranges
Merging cells does not group them. It replaces several cells with one that occupies their space, and the others cease to exist.
What stops working
Sorting. Both products refuse to sort a range containing merged cells, or produce a result that does not preserve the rows, depending on the arrangement.
Filtering. A filter over a range with merged cells shows and hides rows inconsistently, because the rows underneath a merge have no value of their own.
Selecting a column. Selecting one column of a range that contains a horizontal merge selects more than one column, which propagates into every operation performed on the selection.
Converting to a table. Refused outright, which matters because the table object is the single most useful structural improvement described on this site.
Keyboard navigation. Moving across a row skips the merged region as one step, so the arrow key and the cell reference stop corresponding, which is disorienting rather than damaging.
Formulas referring into it. Only the top-left cell holds the value. The others return empty, which produces totals that are correct and lookups that are not.
Why people do it
To centre a title over several columns, and to make a heading span a group of sub-headings. Both are presentation, and both have a solution that does not destroy cells.
The formatting option almost nobody knows
Excel offers horizontal alignment across a selection, which centres the content of the leftmost cell over the whole selection visually while leaving every cell intact and independent.
The appearance is identical to a merge. Sorting, filtering, selection and table conversion all continue to work, because nothing was removed. It is in the alignment tab of the format dialogue, it has been there for decades, and it is the direct replacement for the great majority of merges in existence.
Google Sheets does not offer an equivalent, which is a genuine gap and means the answer there is to accept an uncentred heading or to place it in its own row above the table.
The two-row header
The commonest use of merging in data tables is a heading spanning several columns above their individual names. That produces a two-row header, which is where most of the damage originates: table conversion refuses it, import tools produce columns named after nothing, and structured references cannot exist.
The answer is one header row with names that carry the grouping in the text — sales figures for one quarter labelled with the quarter and the measure in one string — which is uglier and works everywhere.
Finding merges in a file you inherited
Excel's find dialogue can search by format, including merged cells, which lists every one in the workbook. That is the fast route and it is buried behind a button most people have not pressed.
The alternative is to attempt a table conversion on each sheet and read the complaint.
Where merging is acceptable
On a sheet that is purely a printed layout or a form, with no data anybody will sort, filter or import. That is a real category and it is smaller than the number of merged cells in the world implies.
The test is whether any operation on this sheet will ever treat its rows as records. If yes, no merges.
What we cannot verify
The refusal to sort, the table conversion refusal and the alignment option are documented by the vendor and were confirmed on a current build. Google Sheets behaviour on filtering merged ranges and the absence of an equivalent alignment option were established by testing rather than found in documentation stating the absence.
Unmerging a file safely
Unmerging leaves the value in the top-left cell and the others empty, which is usually not what the data means: a merged region spanning four rows of a category column meant that category applied to all four.
So the sequence is unmerge, then fill the empty cells with the value above, then check the row count against what it was. Doing the first without the second produces a table with holes that look like missing data, and the holes are the merge.
Why it persists despite all of this
Because the button is on the toolbar, prominently, and the alternative is three clicks into a dialogue nobody opens. That is the whole explanation, and it is worth stating because the remedy is not education about merging but knowing where the other option lives.
Put the alignment option on the quick access toolbar and the habit changes in a week.
The version that hides rather than removes
Grouping rows or columns collapses them out of view and keeps every cell intact. It looks similar on screen to a merge in one respect, that fewer things are visible, and it breaks nothing at all.
Where the aim is a tidier sheet rather than a centred heading, that is the tool, and it is the one people reach for last.
In short
- Merging replaces several cells with one; the others are gone rather than hidden.
- Sorting, filtering, column selection and table conversion all stop working.
- Only the top-left cell holds the value, so lookups into the region return empty.
- An alignment option gives the same appearance and removes nothing.
- That option exists in one product and not in the other.
- Merged headings produce two-row headers, which is where most of the damage starts.