[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.

Read More: How to Copy Number Not Formula in Excel

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:

• Select the cells C5:D9.
• Right-click and select Format Cells from the Context menu.

A dialog box will open up.

• Click on Alignment and choose Center Across Selection from the Horizontal section.

The names are center-aligned and look like merged cells.

Download Practice Workbook

Related Articles

• [Solved] Excel Copy Paste Loses Formatting
• [Fixed]: Right Click Copy and Paste Not Working in Excel
• [Fixed!] Paste Link Not Working in Excel
• [Fixed!] Copy and Paste Not Working Between Workbooks in Excel
• [Fixed!]: Microsoft Excel Cannot Paste the Data as Picture
• [Fixed!] CTRL C Not Working in Excel

<< Go Back to Copy-Paste Not Working | Copy Paste in Excel | Learn Excel