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

The following picture demonstrates how you can copy into merged cells with formulas.

How to Copy and Paste in Excel with Merged Cells: 2 Ways

Method 1 – Copy and Paste in Excel with Merged Cells Using Paste Special

Unmerged to Merged:

Imagine you have data in some unmerged cells in column B and some merged cells in column D.

You can copy a single cell from column B and easily paste it on a single merged cell in column D.

Copy the entire range of data from column B. Then, try to paste it on the merged cells. This time you won’t be able to do so.

Now, copy the same range of unmerged cells from column B as the range of merged cells in column D. Then, try to paste them on the merged cells. This will unmerge the merged cells.

Merged to Unmerged:

Consider the following dataset instead. Here, some merged cells in column B contain the data.

Copy the merged cells first. Then, paste it on the unmerged cells. This will convert unmerged cells to merged.

If you try to paste them as Values or Formulas & Number Formatting using Paste Special, the result will be as follows.

Method 2 – Copy and Paste in Excel with Merged Cells using INDEX Function

Steps

Unmerged to Merged:

Select the entire range of merged cells.
Copy the following formula and press Ctrl + Enter.

=INDEX($B$5:$B$10,COUNTA(D$5:D5))

You might see the following error message. Hit the OK button.

You will see the following result.

Make sure not to press Enter alone or you will get an incorrect result.

Select File and choose Options.
Go to the Formulas tab.
Check the checkbox for Enable Iterative Calculation.
Hit the OK button.

The unmerged cells will be copied to the merged cells as follows.

Merged to Unmerged:

Select the same range of unmerged cells as the merged cells.
Copy the following formula and press Ctrl + Enter.

=INDEX($B$5:$B$10,COUNTA(D$5:D5))

You will see the following result.

Copy the entire range of unmerged cells and paste it there as Values.

Select cell E4.
Go to Sort & Filter from the Home tab and pick Filter.
Select the drop-down arrow in cell E4.
Uncheck 0 and hit OK.

You will see the following result.

An Alternative to Merging Cells in Excel

Steps

Suppose you want the text in cell B2 to be center-aligned in between cells B2 and D2.

Select cells B2 to D2.

Right-click and select Format Cells. This will open a new dialog box.

Go to the Alignment tab.
For Horizontal alignment, click on the dropdown arrow and choose Center Across Selection.
Hit the OK button.

The cells are still unmerged, yet the texts are properly aligned as desired as follows.

Things to Remember

You can always unmerge the merged cells first to copy and paste without any problem.
Do not forget to use Ctrl + Enter to apply the INDEX function.

Download Practice Workbook

You can download the practice workbook from the download button below.

Copy Paste Merged Cells.xlsx

<< Go Back to Copy Paste in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Sub CopyMergedCells() Dim mergedRange As Range, cell As Range Dim unmergedRange As Range, destCell As Range Set mergedRange = Selection 'Assuming the merged cells are currently selected Set unmergedRange = mergedRange.Cells(1).Resize(mergedRange.Rows.Count * mergedRange.Columns.Count, 1) Set destCell = Application.InputBox(prompt:="Select the top-left cell of the destination range", Type:=8) If destCell Is Nothing Then Exit Sub 'User cancelled the selection For Each cell In unmergedRange.Cells If cell.Value <> "" Then destCell.Value = cell.Value Set destCell = destCell.Offset(1, 0) End If Next cell End Sub

2 Comments

singgih wahyu n Mar 23, 2023 at 11:40 PM

i have a question
how can i copy data from merged cells to unmerged cells without including the blank data ?
as you shown in your tutorial, the blank data is still there and need filtering to get rid of it
Thank you!
- Reply
  Rafiul Hasan Mar 27, 2023 at 2:26 PM
  Hi SINGGIH WAHYU N,
  You can use VBA code for serving your requirement.
  1. Select the merged cell(s) that contain data you want to copy.
  2. Open the Visual Basic Editor by pressing Alt + F11.
  3. Insert a new module by selecting “Insert” -> “Module” from the menu bar.
  4. In the new module, enter the following code:
```
Sub CopyMergedCells()
    Dim mergedRange As Range, cell As Range
    Dim unmergedRange As Range, destCell As Range
    Set mergedRange = Selection 'Assuming the merged cells are currently selected
    Set unmergedRange = mergedRange.Cells(1).Resize(mergedRange.Rows.Count * mergedRange.Columns.Count, 1)
    Set destCell = Application.InputBox(prompt:="Select the top-left cell of the destination range", Type:=8)
    If destCell Is Nothing Then Exit Sub 'User cancelled the selection
    For Each cell In unmergedRange.Cells
        If cell.Value <> "" Then
            destCell.Value = cell.Value
            Set destCell = destCell.Offset(1, 0)
        End If
    Next cell
End Sub
```
  5. Run the code and a prompt will ask you to select the first cell of destination range. Select a cell where you want to paste values and you will get output.
  
  Hope this help you. If you don’t want to use VBA, you have to first paste values and then use the “Go To Special” (pressing CTRL+G) dialog box and then select “Blanks”> click “OK” to remove blank cells.
  
  Regards.
  Rafiul Hasan
  Team ExcelDemy