When creating a table in Excel, many people organize the headings neatly by ``merging cells and centering them.'' But aren't you having problems such as "unable to copy," "unable to sort," and "filter is incorrect" due to merged cells?

Actually, cell merging in Excel looks pretty, but it's like a "land mine" that causes a lot of problems behind the scenes. In this article, we will explain in an easy-to-understand manner the causes of troubles that occur when merging cells, and how to use the alternative technique ``center within selection'' to keep the appearance clean without merging, based on the latest Excel (Microsoft 365 / Excel 2024) information as of February 2026.

5 "problems" that occur when merging cells

First of all, let's find out the true reason behind "Why can't I copy!?" The following five problems are mainly caused by cell merging.

1. "This operation cannot be performed on merged cells" error when copying and pasting

When you copy a range that contains merged cells and try to paste it elsewhere, you may receive an error that says "This operation cannot be performed on merged cells." This occurs when the cell merging patterns (how many rows and columns are combined) of the copy source and paste destination do not match.

In other words, if you try to paste the contents of a cell with 3 rows merged into a cell with only 2 rows merged, it will not work.

2. Unable to autofill (continuous copy) of formulas

If there are merged cells, "Autofill", which copies the formula downward by dragging the bottom right of the cell, does not work properly. Formulas may be skipped or entered in unintended cells.

3. Unable to sort.

If you try to sort the data in ascending or descending order, the message "To perform this operation, all merged cells must be the same size" appears and the sort is rejected. As long as merged cells are included, sorting functionality is almost impossible.

4. Filter (autofilter) does not work properly

If you apply a filter, only the first row of the merged cells will be displayed and the remaining rows will be hidden. If three rows are combined, only the first row of data will appear in the filter result.

5. Pivot tables and functions (VLOOKUP, XLOOKUP, etc.) malfunction

If you specify a range that includes merged cells as a PivotTable data source, the range may be recognized as blank cells. Also, XLOOKUP or VLOOKUP, the second and subsequent rows of the merge will be treated as "blank" and the correct value will not be returned.

Why does cell merging cause so much trouble?

The reason is simple. When merging cells in Excel, it looks like one cell visually, but internally, only the top left cell contains data, and the other cells are empty.

For example, if you join A1:A3 and enter "Sales Department", this is what you would actually get:

  • A1 → “Sales Department” (with data)
  • A2 → Blank (not visible but empty)
  • A3 → Blank (not visible but empty)

This "gap between appearance and content" is the cause of all troubles with copying, sorting, filters, and functions. Excel's functions are basically designed on the assumption that "1 cell = 1 data", so merging cells breaks that assumption.

I want to copy and paste even in merged cells! 3 first aid measures

Here are three emergency measures for people who want to do something about this table right away.

Method 1: Select "Paste Special" → "Value" or "Formula"

Instead of the regular Ctrl+V, paste as follows:

  1. Select the cells you want to copy Ctrl+C
  2. Select the cell you want to paste into
  3. Press Ctrl+Alt+V (Paste Special)
  4. Select “Value” or “Formula” and OK

Now you can ignore the "frame" information of the merged cell and paste only the data inside.

Method 2: Unbind all at once and then copy

  1. Select target cell range
  2. “Home” tab → Click ▼ (dropdown) for “Merge and center cells”
  3. Select "Unmerge cells"
  4. Copy and paste after release

If you cancel, the data will remain only in the upper left cell, and the rest will be blank. Fill in the blank cells as necessary.

Method 3: Align the bonding patterns of the copy source and paste destination

If the copy source is "3-line join", you can copy as is by setting the paste destination to the same "3-row join". However, this isnot a fundamental solution (it just increases the number of merged cells). It's just a way to "get through this moment."

Fundamental solution! Create a beautiful table without joining with "Center within selection"

The best way to fundamentally eliminate problems with merged cells is to not merge cells in the first place. There's no need to worry about "but it's going to look uncool...". You can use the Center in Selection feature to center text without merging cells.

Setting procedure (common for Windows / Mac)

  1. Select the cell range you want to center (e.g. A1:C1)
  2. Enter characters only in the leftmost cell (A1)
  3. Ctrl+1 (⌘+1 on Mac) to open Format Cells
  4. Select the Placement tab
  5. Select Center in Selection from the Horizontal Position dropdown
  6. Click OK

With this alone, the text will be displayed in the center as if the cells were merged, even though the cells are not merged. Moreover, copy, sort, filter, and functions all work properly!

Advantages of "center within selection"

  • Copy and paste is possible
  • Sort/filter works properly
  • Functions (VLOOKUP, XLOOKUP, SUMIF, etc.) work correctly
  • Cell selection/movement doesn't get stuck
  • Giving the file to someone else is less likely to cause trouble

Microsoft's official help also introduces "Center within selection" as a method for arranging cells.

How to cancel existing merged cells at once and replace them with "center within selection"

For those who think, "I have files that have already been combined...", I will introduce the procedure for replacing them all at once.

Step 1: Find merged cells

  1. Open Ctrl+H (Find and Replace)
  2. Click Options on the Search tab
  3. Click the “Format” button → “Placement” tab
  4. Check "Merge cells" and OK
  5. “Search All” displays a list of merged cells

Step 2: Break the bond

  1. Select entire sheet (Ctrl+A)
  2. "Home" → "Merge and center cells" ▼ → "Unmerge cells"

Step 3: Fill in blank cells

If you cancel the join, the second and subsequent lines will be blank. To select blank cells and enter the same value as the cells above:

  1. Select target column
  2. Ctrl+G (Jump) → "Cell selection" → Select "Blank cell" and OK
  3. With only blank cells selected, enter = (equal) and press the ↑ (up arrow) key
  4. Ctrl+Enter for bulk input

All cells in the merged range will now be filled with data. After that, just set "Center within selection" for only the necessary headings.

FAQ

In which versions of Excel can you use "Center within selection"?

Available in all versions of Excel 2007 and later (Excel 2010, 2013, 2016, 2019, 2021, 2024, Microsoft 365). It appears in the "Horizontal Position" dropdown on the "Format Cells" > "Alignment" tab. You can also use the Mac version of Excel in the same way.

What is the safest way to deal with receiving a file containing merged cells?

It is safe to first back up the original file and replace it with the copied file in the following order: "Unmerge cells" → "Fill blank cells" → "Center within selected range". If you keep the original file, you can restore it even if the layout gets corrupted.

Does the same problem occur with Google Sheets?

Yes, the problem of sorting and filtering due to cell merging also occurs in Google Sheets. However, as of February 2026, Google Sheets does not have a function that fully corresponds to "center within selection", so it is best to avoid merging and devise layouts (by using borders and indentation).

Is there a way to unmerge cells at once using a macro (VBA)?

Yes. You can unmerge all merged cells on a sheet at once by running ActiveSheet.Cells.UnMerge in VBA. However, filling in data holes requires separate processing, so if you are not familiar with VBA, we recommend that you cancel it manually.

References