Skip to content
ArumSoft Learning
KO
    SortExcel

    Fix the sort error "merged cells need to be the same size"

    Works in: All Excel versions, including Microsoft 365, Excel for Mac and Excel for the web · Updated

    Quick answerSelect the data, click Home > Merge & Center to unmerge the cells, and sort again. To keep a heading centered without merging, use Center Across Selection.

    Causes and fixes

    1. Merged cells are inside the data
    2. Unmerging leaves blank cells
    3. A merged heading is included in the sort range
    4. 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

    1. Select the column.
    2. Choose Home > Find & Select > Go To Special > Blanks.
    3. Type =, press the Up Arrow, then press Ctrl+Enter to fill every blank with the value above.
    4. 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.