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.
- 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.
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.
- 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.
Read More: How to Copy Merged and Filtered Cells in Excel (4 Methods)
- How to Copy Excluding Hidden Rows in Excel (4 Easy Methods)
- Copy Rows in Excel with Filter (6 Fast Methods)
- Macro to Copy Specific Columns from One Worksheet to Another in Excel
- How to Copy Visible Cells Only without Header Using VBA
- Copy and Paste in Excel When Filter Is On (5 Methods)
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.
- 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.
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.
- 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.
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.
- 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.
Read More: How to Disable Copy and Paste in Excel without Macros (With 2 Criteria)
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.
- VBA Code to Compare Two Excel Sheets and Copy Differences
- [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
- How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
- Difference Between Paste and Paste Special in Excel
- How to Copy and Paste in Excel without Formulas (7 Easy Tricks)
- Copy Rows Automatically in Excel to Another Sheet (4 Methods)
- How to Paste From Clipboard to Excel Using VBA