Microsoft Excel

Why Excel's Merge and Center Is EVIL (And How to Fix It)

Chris Menard

Chris Menard

— 2 min read
Why Excel's Merge and Center Is EVIL (And How to Fix It)

Merge and Center is one of Excel's most misused features. It looks great for formatting headers, but it breaks sorting, filtering, copying, and pasting. In this tutorial, Chris Menard explains exactly why Merge and Center causes problems and shows you the better alternative: Center Across Selection.

Watch the full tutorial:

Why Merge and Center Is a Problem

When you merge cells, Excel combines multiple cells into one. This causes several issues:

  • Sorting breaks — Excel cannot sort a range that contains merged cells. You'll get an error: "To do this, all the merged cells need to be the same size"
  • Filtering breaks — Filters skip rows that are part of a merged cell range
  • Copy and paste breaks — You can't paste data into a range that partially overlaps with merged cells
  • Data loss risk — When merging cells that contain data, Excel keeps only the upper-left cell's value and deletes everything else
Excel spreadsheet with quarterly sales data for Atlanta, Cleveland, and London using merged cells for city headers
A typical layout using Merge and Center for city headers — looks nice, but causes problems.

How to Find All Merged Cells

If you've inherited a spreadsheet full of merged cells, here's how to find them all:

  1. Press Ctrl+H to open Find and Replace
  2. Click Options to expand the dialog
  3. Click the Format button next to the "Find what" field
  4. Go to the Alignment tab
  5. Check the Merge cells checkbox
  6. Click Find All to see every merged cell in the workbook
Excel Find and Replace dialog with Format options showing Merge cells checkbox
Use Find & Replace with Format options to locate all merged cells in your workbook.

How to Fix It: Unmerge and Use Center Across Selection

The fix is a two-step process:

Step 1: Unmerge All Cells

  1. Select the entire range with merged cells (or press Ctrl+A for the whole sheet)
  2. Go to Home > Merge & Center dropdown
  3. Click Unmerge Cells
  4. The data will now be in the top-left cell of each formerly merged range, with blank cells beside it

Step 2: Apply Center Across Selection

  1. Select the cells you want the text centered across
  2. Press Ctrl+1 to open Format Cells
  3. Go to the Alignment tab
  4. Under Horizontal, choose Center Across Selection
  5. Click OK

The result looks identical to Merge and Center, but the cells remain individual. Sorting, filtering, and copy-paste all work perfectly.

Excel spreadsheet showing sorted data with Center Across Selection applied instead of merge
Center Across Selection achieves the same look without breaking sort and filter.

Want to learn more? Visit courses.chrismenardtraining.com for online training courses.

Read more