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

Get FREE Advanced Excel Exercises with Solutions!

Copy and paste with merged cells in excel is a bit tricky. It often either results in an error or excel doesn’t allow it. But fortunately, there are a few ways around doing this. This article shows 2 ways how to copy and paste in excel with merged cells. The following picture gives an idea of the purpose of this article.

How to Copy and Paste in Excel with Merged Cells


Download Practice Workbook

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


2 Ways to Copy and Paste in Excel with Merged Cells

Here, I am going to illustrate two easy ways to copy and paste in excel with merged cells. So, let’s begin!


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.

  • Now, you can copy a single cell from column B and easily paste it on a single merged cell in column D.

  • Now, 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. But, this will unmerge the merged cells.

Merged to Unmerged:

  • Now, consider the following dataset instead. Here, some merged cells in column B contain the data.

  • Now, copy the merged cells first. Then, paste it on the unmerged cells. This will convert unmerged cells to merged.

  • After that, try to paste them as Values or Formulas & Number Formatting using Paste Special. Then, the result will be as follows.

How to Copy and Paste in Excel with Merged Cells using Paste Special

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


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

Follow the steps below to learn how to copy and paste in excel with merged cells using the INDEX function.

📌Steps

Unmerged to Merged:

  • First, select the entire range of merged cells. Then, type the following formula while the cells are selected and press CTRL + Enter.
=INDEX($B$5:$B$10,COUNTA(D$5:D5))
  • Then, you may see the following error message. Don’t worry. Just hit the OK button.

  • After that, you will see the following result.

How to Copy and Paste in Excel with Merged Cells using INDEX Function

  • DO NOT PRESS ENTER ALONE! Rather press CTRL+Enter. Otherwise, the following will occur.

  • Next, select File >> Options. Then, go to the Formulas tab. After that, check the checkbox for Enable Iterative Calculation. Then, hit the OK button.

  • Finally, the unmerged cells will be copied to the merged cells as follows.

Merged to Unmerged:

  • First, select the same range of unmerged cells as the merged cells. Then, type the following formula and press CTRL+Enter.
=INDEX($B$5:$B$10,COUNTA(D$5:D5))
  • After that, you will see the following result.

How to Copy and Paste in Excel with Merged Cells using INDEX Function

  • Then, copy the entire range of unmerged cells and paste it there as Values.

How to Copy and Paste in Excel with Merged Cells using Paste Special

  • Next, select cell E4. Then, select Sort & Filter >> Filter from the Home tab.
  • After that, select the drop-down arrow in cell E4. Next, uncheck 0 and hit OK.

  • Finally, you will see the following result.

How to Copy and Paste in Excel with Merged Cells

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


An Alternative to Merge Cells in Excel

Merging cells in excel is never recommended. It causes problems in copy-paste and especially in Excel VBA. You don’t need to merge and center cells to properly align your data. Follow the steps below to see the alternative way.

📌 Steps

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

  • First, select cells B2 to D2 to do that.

  • Then, right-click and select Format Cells. This will open a new dialog box.

  • After that, go to the Alignment tab. For Horizontal alignment, click on the dropdown arrow and choose Center Across Selection. Then, hit the OK button.

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

Conclusion

Now you know the ways around how to copy and paste in excel with merged cells. Please use the comment section below for further queries and suggestions. Excel is not mainly designed to display data rather store them. So, we recommend you not to merge cells in Excel. You can rather use the cell formatting as explained above. Please visit our Exceldemy blog and keep learning more and more about excel.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

2 Comments
  1. 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!

    • 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo