How to merge cells in Excel — Merge & Center, the sort error, a filter that shows one row, and centering without merging

Merge & Center — only the upper-left value survives

Drag over the cells to combine and click Home > Alignment group > Merge & Center. They become one cell with the text centered. If two or more of the selected cells hold values, a warning comes first.

The ▾ next to Merge & Center offers Merge & Center, Merge Across, Merge Cells and Unmerge Cells. Merge Across merges each row of the selection separately — merging E1:G2 across gave two cells, E1:G1 and E2:G2, each keeping its own left value (e1, e2). Merge Cells merges without centering; Unmerge Cells splits a merged cell again.

Trap 1 — a table with merged class cells won't sort

The practice file is a score sheet with Class (column A) merged three rows at a time and the title merged across A1:C1. It looks tidy, but sorting and filtering break.

With a score cell (C4) selected, Home > Sort & Filter > Sort Largest to Smallest brought up the error 'To do this, all the merged cells need to be the same size.' After OK the table hadn't changed at all. The sort range mixes three-row merged cells (column A) with single cells (columns B and C).

Trap 2 — the filter shows one row out of three

On the same sheet, filtering the Class column to Class 1 showed only Kim — one of the three Class 1 students. The status bar said '1 of 6 records found'.

A merged cell's value is stored in its first cell only (A3); A4 and A5 are empty. Unmerge it and you can see it — A3 kept 'Class 1' and A4 and A5 were blank (A6 to A8 likewise). So the filter keeps only the A3 row.

The fix — unmerge, then fill the blanks with the value above

1. 1Select A3:A8 and choose Home > Merge & Center ▾ > Unmerge Cells. Only A3 and A6 keep a class; four cells are blank.
2. 2With A3:A8 still selected, Home > Find & Select > Go To Special… > Blanks > OK. Only the four blanks (A4:A5, A7:A8) are selected.
3. 3Type = and press ↑ (the cell above) and then Ctrl+Enter. Every selected blank gets 'the cell above' at once.
4. 4Copy A3:A8 and Paste Values so the formulas become text (Class 1, Class 2) and sorting can't shift them.

After that, filtering to Class 1 showed all three students (3 of 6), and sorting by score worked without an error: 95, 91, 88, 82, 77, 70. That is the answer file.

Center without merging — Center Across Selection

If you only want a title centered over a table, use this instead of merging. Select A1:C1 and press Ctrl+1 (Format Cells) > Alignment > Horizontal: Center Across Selection > OK.

On this PC the title then looked centered over three columns but was not a merged cell (A1 not merged, B1 still empty), and sorting the same range raised no error. For a value repeated over several rows, like the class, keep it in every row instead of merging — that's what kept sorting and filtering working.

Common problems

• Text disappeared after merging: only the upper-left value is kept — join values first with =A1&" "&B1
• 'All the merged cells need to be the same size' when sorting: merged cells of different sizes in the range — unmerge and fill the blanks
• The filter shows only one row: the merged value is in the first cell only — unmerge and fill the blanks
• Can't find unmerge: the ▾ next to Merge & Center > Unmerge Cells
• Just want a centered title: Format Cells > Alignment > Center Across Selection

Practice

Open the practice file (score sheet with the title and classes merged) and try these steps.

1. 1Select a score cell and sort largest to smallest — does the error appear?
2. 2Turn on a filter and show Class 1 only — how many rows do you see? (One on this PC.)
3. 3Clear the filter, unmerge A3:A8, Go To Special > Blanks, =A3 (the cell above), Ctrl+Enter, then Paste Values.
4. 4Sort and filter again, then unmerge the title A1:C1 and use Center Across Selection.

The answer file has a class in every row, sorted by score from largest to smallest with the filter on, and a title centered with Center Across Selection.

FAQ