Fix the sort error "merged cells need to be the same size"
Causes and fixes
- Merged cells are inside the data
- Unmerging leaves blank cells
- A merged heading is included in the sort range
- Use Center Across Selection instead of merging
1Merged cells are inside the data
Excel sorts row by row. A cell merged across two rows belongs to both, so Excel can't move the rows separately.
How to find them
Press Ctrl+F, click Options > Format, open the Alignment tab and tick Merge cells, then click Find All. Press Ctrl+A in the results to select them all.
Fix
With them selected, click Home > Merge & Center to unmerge.
2Unmerging leaves blank cells
After unmerging, only the top cell keeps the value. The rows below are blank, so they sort to the wrong place.
Fix
- Select the column.
- Choose Home > Find & Select > Go To Special > Blanks.
- Type
=, press the Up Arrow, then press Ctrl+Enter to fill every blank with the value above. - Copy the column and use Paste Special > Values to keep the values.
3A merged heading is included in the sort range
A title merged across the top of the table gets pulled into the sort when you select whole columns.
Fix
Select only the table, from the header row down, before sorting. Or turn it into an Excel table with Insert > Table; its sort buttons only use the table rows.
4Use Center Across Selection instead of merging
Center Across Selection looks the same as Merge & Center but keeps every cell separate, so sorting, filtering and copying keep working.
Fix
Select the cells, press Ctrl+1, open Alignment and choose Horizontal > Center Across Selection.
Still stuck?
- Filtering and copying a column have similar problems with merged cells.
- If the message mentions a table, you may have merged cells inside an Excel table: tables don't allow them.
- Sorting part of a list? Excel asks whether to expand the selection; choose Expand the selection so rows stay together.