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


Download Practice Workbook

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


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.

Double Click and Copy Text then Paste to a Single Cell

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.

Cannot copy merged cells in excel

Read More: How to Copy and Paste in Excel with Merged Cells (2 Methods)


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.

Apply Paste Special If You Cannot Copy Merged Cells to a Single Cell in Excel

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

Apply Paste Special If You Cannot Copy Merged Cells to a Single Cell in Excel

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.

Apply Paste Special If You Cannot Copy Merged Cells to a Single Cell in Excel

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

Apply Paste Special If You Cannot Copy Merged Cells to a Single Cell in Excel

Read More: How to Copy Merged and Filtered Cells in Excel (4 Methods)


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.

Apply Paste Special If you Cannot Copy Merged Cells to Separate Cells in Excel

Steps:

  • Copy the merged cells B5:B8.

Apply Paste Special If you Cannot Copy Merged Cells to Separate Cells in Excel

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

Apply Paste Special If you Cannot Copy Merged Cells to Separate Cells in Excel

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

Apply Paste Special If you Cannot Copy Merged Cells to Separate Cells in Excel

Read More: Formula to Copy and Paste Values in Excel (5 Examples)


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.

Embed Excel VBA to Copy And Paste Merged Cells To a Single Cell

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

Embed Excel VBA to Copy And Paste Merged Cells To a Single Cell

Here’s the output after running the VBA codes.

Read More: Copy and Paste is Not Working in Excel (9 Reasons & Solutions)


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.

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

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

Read More: How to Disable Copy and Paste in Excel without Macros (With 2 Criteria)


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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo