Why merging cells sucks

What are merged cells?

Microsoft telemetry suggests that over half of all Excel workbooks contain merged cells, so most likely you know the answer to this already. But just in case you don't:

The Merge & Center menu, shown here as part of the Home menu, allows you to merge two or more cells into one large cell. The default setting also horizontally centres the resulting text relative to the new, larger cell. They are often used to centre headings across several related columns.

Why are merged cells so bad?

There are a host of annoying problems that crop up once a workbook has merged cells in it.

Formulas

Formulas that refer to the merged cell will default to using the cell reference for the top-left-most cell in it. Formulas addressing the other cells in the merge can still be written but will return a value of 0. This makes creating nice, flexible formulas much harder, as row/column values become unreliable.

Delete or move rows / columns

Deleting a range that contains a merged cell will remove the data only if the top-left cell of the merge is deleted. Otherwise, the range will be shrunk in size and the data will remain. This behaviour is hard to predict and can be counterintuitive.

Pasting data

You can't paste single-column data into a column that has a merged cell in it, because the destination range is not considered to be the same size and shape as the origin range.

Filtering

Filters don't work properly on ranges with merged cells in them.

Selecting rows / columns

You can't use the keyboard shortcuts / mouse to select a range that contains a merged cell without automatically also selecting any other rows / columns that include the merge. This can be particularly bad if the new rows / columns contain more merged cells, cascading through and making the selection wildly separate from what you originally intended.

So how can you work around these issues?

First of all, if a spreadsheet is 100% presentational, and has no data or formulas, then merged cells can be appropriate. But this is rare.

If you still want to have neat column labels, you can try entering the heading in the first column, selecting the desired range for the headings, and then using 'Center Across Selection' from the Format menu:

This will centre the text visually but leave the actual data in just the first cell.

Alternatively, use repeated column headings, coloured sections, or other visual cues to identify different batches of related columns.

Finally, if you already have merged cells in a workbook and want to get rid of them, you can select the entire sheet and then use the Merge button to unmerge all cells. Note that the data from the merged cells will be dumped into the top-left cell when you do this, with the other cells being left blank.