[Fixed!] Cannot Copy Merged Cells in Excel (4 Solutions)

For illustration, we will use this sample dataset which represents the 5 bestseller books of 2020 at amazon.com. The book names are merged between columns C and D.

Solution 1 – Double Click and Copy Text then Paste to a Single Cell

Steps:

Double-click the merged cells C7:D7.
Select the text and copy it.

Copy it to the desired cell, e.g., Cell D11.

The text is copied to only Cell D11.

Solution 2 – Apply Paste Special If You Cannot Copy Merged Cells to a Single Cell

Steps:

Select and copy the merged cells C7:D7.

Right-click on desired cell, e.g., Cell D11.
Select Paste Special from the Context menu.

In the Paste Special dialog box-

Mark Values and number formats from the Paste section and mark None from the Operation section.
Press OK.

The merged cells will be copied to a single cell.

Solution 3 – Apply Paste Special If You Cannot Copy Merged Cells to Separate Cells

In this section, we’ll copy merged cells to separate single cells. The sample dataset has merged cells B5:B6 and C5:C6.

Steps:

Copy the merged cells B5:B8.

Right-click on desired cell, e.g., Cell D11.
Select Values from the Paste options of the Context menu.

The merged cells will be copied as unmerged cells like in the image below.

Solution 4 – Embed VBA to Copy and Paste Merged Cells to a Single Cell

Steps:

Right-click on the sheet title.
Select View Code from the Context menu.

Or press Alt+F11 to open the VBA window directly.

Enter the following codes in the VBA window-

Sub CopyMergedCells()
Cells(7, 3).Copy
Cells(11, 4).PasteSpecial Paste:=xlPasteValues
End Sub

Click the Run icon to run the codes.

Here’s the output after running the VBA codes.

Use Center Across Selection to Avoid Copy/Paste Merged Cell Errors

You can smartly avoid the copy/paste merged cells related problems by using an amazing tool – Center Across Selection in Excel. It replicates the look of merged cells while not actually being merged.

Steps: