"To do this, all the merged cells need to be the same size"
Sort, filter or pivot table refuse to work because of merged cells. Unmerging in Excel leaves empty cells, which then have to be filled by hand.
What the message says
A range to sort contains merged cells of different sizes: Excel does not know how to move a cell that occupies three.
Why it appears
- Group titles merged over several rows (one region for five customers).
- Headers merged over several columns.
- A table laid out for printing, not for calculation.
Making it go away
- Select the range, Merge & Center to unmerge: values stay only in the first cell.
- Fill the blanks: Go To Special, Blanks, then
=and the cell above, Ctrl+Enter, and convert to values. - Prefer "Center Across Selection" to merging for titles.
The tool that handles it
Unmerge cells removes every merge, sheet by sheet, and copies the value into each freed cell, formatting included: the table sorts and filters at once.
Frequently asked questions
- Does unmerging lose data?
- No: a merged cell only keeps the value of its first cell anyway. Unmerging gives it back to all of them.