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

You must face some unwanted problems when you are going to copy and paste merged cells in Excel. Because there are some conditions of merged cells. Hope this article will be a useful guide to rescue you from the problems when you cannot copy merged cells in Excel.

Table of Contents hide

Download Practice Workbook

4 Solutions: Cannot Copy Merged Cells in Excel

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

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

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

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

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

Conclusion

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Cannot Copy Merged Cells.xlsm

4 Solutions: Cannot Copy Merged Cells in Excel

To explore the methods we’ll use the following dataset which represents the 5 bestseller books of 2020 at amazon.com. The books’ names are merged between Columns C and D.

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

If you copy merged cells and then paste, it will be copied but it will be pasted as merged cells. But maybe you want to copy just in a cell. So now let’s see how we can solve this problem by using this method. I’ll copy merged cells of row 7.

Steps:

Double click the merged cells C7:D7.
Then select the text and copy it.

Now, I’ll copy it to Cell D11.

Just click the cell and paste.

Then you will see that the text is copied to only Cell D11.

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

Now we’ll use the Paste Special command of Excel to copy and paste merged cells to a single cell.

Steps:

Select and copy the merged cells C7:D7.

Then right-click Cell D11.
Select Paste Special from the Context menu.

After appearing the Paste Special dialog box-

Mark Values and number formats from the Paste section and mark None from the Operation section.
Finally, just press OK.

Now have a look that Excel has copied the merged cells to a single cell.

Similar Readings

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

In this section, we’ll copy merged cells to single cells that means after copying it will take the same number of cells but will be unmerged. To show that, I have edited the dataset. I have merged cells B5:B6 and C5:C6. Now let’s copy those merged cells.

Steps:

Copy the merged cells B5:B8.

Right-click Cell B11.
Select Values from the Paste options of the context menu.

Then you will get that Excel has copied the merged cells as unmerged cells like the following image below.

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

If you like to work with coding in Excel then also you can copy and paste merged cells to a single cell in Excel using VBA Macro. We’ll copy the merged cell C7:D7 to Cell D11.

Steps:

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

Soon after, a VBA window will appear. Or you can press Alt+F11 to open the VBA window directly.

Later, write the following codes in the VBA window-

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

Finally, just 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 will look like merged cells but not merged actually.

Steps:

Select the cells C5:D9.
Then right-click your mouse and select Format Cells from the context menu.

Later, a dialog box will open up.

Then click on Alignment and choose Center Across Selection from the Horizontal section.

Now have a look that the names are center-aligned and look like merged cells.

Conclusion

I hope the procedures described above will be good enough to solve the problem when you cannot copy merged cells in Excel. Feel free to ask any question in the comment section and please give me feedback.