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

Get FREE Advanced Excel Exercises with 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.


Cannot Copy Merged Cells in Excel: 4 Solutions

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 the 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


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


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


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


Download Practice Workbook

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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo