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.
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.
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.
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
- How to Copy Number Not Formula in Excel
- [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
