Can't merge cells in Excel: causes and fixes

1. Merge & Center is greyed out

Check these in order. The first one catches most people.

1. Inside a table: Excel tables can't contain merged cells. Click in the table and choose Table Design > Convert to Range if you really need the merge, but you lose the table's filters, banding and auto-expanding formulas.
2. Editing a cell: if the cursor is blinking in a cell or the formula bar, most of the ribbon is disabled. Press Enter or Esc.
3. Protected sheet: go to Review > Unprotect Sheet.
4. Old shared workbook: if the title bar says Shared, go to Review > Share Workbook (Legacy) and untick sharing. Modern co-authoring through OneDrive doesn't block merging.

2. "Merging cells only keeps the upper-left value"

Merge three cells that each hold a name and Excel warns that it will keep only the upper-left value and discard the others. Click OK and Ben and Chloe are gone; Ctrl+Z brings them back.

To keep everything, join the text into one cell first, paste it as values, then merge or simply widen the cell. TEXTJOIN puts a separator between the items and skips empty cells.

3. Why merged cells cause trouble later

Merging the region labels down column A looks tidy, but only the top cell of each block holds the value; the cells under it are empty. Any formula that reads column A sees that.

Sorting a range with merges of different sizes fails with "To do this, all the merged cells need to be the same size." Copying and pasting into merged areas gives "We can't do that to a merged cell." Filtering shows only the first row of each merged block. Selecting one column selects every column a merge touches. A dynamic array formula can't spill into merged cells and shows #SPILL!. And AutoFit won't set the row height for wrapped text in merged cells.

4. Use Center Across Selection for titles

For a heading over several columns, Center Across Selection looks exactly like Merge & Center but leaves every cell separate, so sorting, copying and column selection keep working. Type the title in the left cell only.

It only works across columns. For labels down a column, repeat the label on every row instead (it's what formulas, filters and pivot tables need), and if the repeats look noisy, set those cells' font to light grey or use a pivot table in Tabular form.

1. Select the title cell and the cells to its right, for example A1:F1.
2. Press Ctrl+1 to open Format Cells and go to the Alignment tab.
3. Set Horizontal to Center Across Selection and click OK.

5. Find the merged cells in a sheet

If sorting or pasting complains and you can't see why, find every merge at once, then unmerge them.

1. Press Ctrl+F and click Options, then Format.
2. On the Alignment tab, tick Merge cells and click OK.
3. Click Find All, then press Ctrl+A to select every merged cell in the list.
4. Click the arrow next to Home > Merge & Center and choose Unmerge Cells.

6. Unmerge and fill the blanks so formulas work

After unmerging column A, only the first row of each block has a label. Fill the gaps with the label above, then paste the column as values. With the labels filled, the same SUMIFS counts every rep. As a formula alternative in a helper column, LOOKUP("zzz",A$2:A2) returns the last text value in the range so far, which is the label for that row.

1. Select A2:A9 and choose Home > Merge & Center (arrow) > Unmerge Cells.
2. With the range still selected, choose Home > Find & Select > Go To Special > Blanks > OK.
3. Type = and press the Up arrow, then press Ctrl+Enter to fill every blank with the cell above.
4. Copy the column and paste it as values (Ctrl+Alt+V, then V and Enter) so the labels stay put if you sort.