Excel: Can’t copy data from merged cell

Key Takeaways

If you can’t copy data from a merged cell in Excel, try one of these options:

Double-click the cell and copy the raw data.
Otherwise, unmerge the cells first, copy the data, then merge the cells again.

You can merge multiple cells in Microsoft Excel, allowing you to align text across multiple rows or columns. However, merged cells can cause formatting problems in your spreadsheet, especially if you want to sort and filter your data. So we advise against merging cells unless you absolutely have to.

In this blog, we will be looking at solutions to help you copy data from merged cells in Excel. We will also look at how to merge and unmerge cells. So please continue reading to learn how to copy data from a merged cell in Microsoft Excel.

Do this if you can’t copy data from a merged cell in Excel:

  • Method 1: Double click into the cell and copy the text.
  • Method 2: Unmerge the cells and copy distinct cells.
  • Method 3: Recreate the appearance of a merged cell using “Center Across Selection.”
  • Method 4: Use third-party solutions, such as Ablebits or Kutools.
  • Get Excel for just $6.99 per month from the Microsoft Store!

Please continue reading to learn everything you need to know about copying data from merged cells in Microsoft Excel.


What is a merged cell in Excel and why can’t you copy data?

In Excel, you can join multiple cells together to create one, merged cell. That may be useful for attributing a single value to multiple cells (we will expand upon this later) or for aesthetic purposes.

When using the normal method of copying a cell with a merged cell, you will paste the whole merged cell, not just the value inside it. However, there are ways of copying only the data inside the merged cell into a single cell.


How to merge cells in Excel (Web & Desktop)

Before we show you how to copy data from a merged cell, it’s worth looking at how to merge a cell properly. Merging cells with existing data inside them will only retain the data in the top-left cell, while all other data will be cleared. Therefore, we advise that you only merge empty cells or a single used cell with empty cells.

In the example below, we want to merge the cells for “Blue Street” and “Green Street” as the occupants all live on the same street.

  • Click on a cell and drag to encompass additional cells.
  • Then, click “Merge & Center” in the “Alignment” section.
  • Click “OK” to complete the action.

  • We can also merge “Number” (column B) and “Street” (Column C) into a single column.
  • Select the cells you want to merge and click on “Merge & Center.”
  • You can also click the down arrow next to “Merge & Center” and select “Merge Across.”

  • To unmerge cells, select the merged cell and click the drop-down arrow next to “Merge & Center.”
  • Select “Unmerge Cells” from the drop-down menu.


Method 1: How to copy data from a merged cell in Excel by clicking into the cell contents

A simple method for copying data from a merged cell is to click into the cell contents and copy the text from inside. That way, the merged cell formatting will not be copied.

  • Open Excel in the web or desktop version and open the workbook with the merged cells.
  • Double click on the merged cell to gain access to the contents.
  • Click and drag to select the cell data or press Ctrl + A on your keypad.
  • With the data selected, press Ctrl + C on your keypad.
  • Then select an empty cell and press Ctrl + V on your keypad.

First, we pasted the merged cell to demonstrate the effect. The effect was that the cell’s format was pasted, so that the three cells C8, C9, and C10 were merged together to contain the pasted cell.

Instead, if you want to paste “Blue Street” into a single cell, you can double click the cell to gain access to the contents and copy and paste the raw text.


Method 2: How to copy data from a merged cell in Excel by unmerging the cells

Unmerging the cell will cause a temporary loss of format. However, you can merge the cells again after you’ve copy and pasted the data from the desired cell.

  • Select your merged cell. (1)
  • Click on the down arrow next to “Merge & Center.” (2)
  • Select “Unmerge Cells” from the drop-down menu. (3)
  • Now click on the cell you want to copy and press Ctrl + C on your keypad.
  • Then click on an empty cell and press Ctrl + V.

After you’ve copied and pasted the data to a new cell, you can merge the cells again by selecting them and clicking “Merge & Center” in the ribbon.


Method 3: How to copy data from a cell that has the appearance of a merged cell

Merging cells in Excel may cause problems when copying and filtering data. To avoid that, you can create the appearance of a merged cell using “Center Across Selection.” Please follow the steps below to learn how.

info Note: Center Across Selection only works horizontally. That means you can only use it to merge columns.

  • First, select the cells you want to merge. (1)
  • Click the popout icon in the “Alignment” section. (2)
  • Click the down arrow under “Horizontal.” (3)
  • Select “Center Across Selection” from the drop down menu. (4)
  • Click the “OK” button to complete the action. (5)

In this example, we have merged B1 and C1 into an “Address” column. However, the columns are still distinct. The effect is achieved by centralizing the text in B1 across the B1/C1 border to give the appearance of merged cells.

The result looks like a merged cell:

Creating the appearance of a merged cell means that the data can be copied to other cells without any problems with the size or format.


Method 4: Use third-party software if you can’t copy data from merged cells in Excel

For this method, we will focus on two extensions or add-ins for Microsoft Excel; Ablebits and Kutools. Overall, we found that Kutools was easier to use when extracting data from a merged cell. Kutools is also cheaper and has a longer trial period.

Ablebits Ultimate Suite

Ablebits is an expansion for Excel with over 70 professional tools with 300+ options. You can download the software with a 14-day free trial, which grants you access to all the features you need to copy data from merged cells.

Over 300 additional tools and options
Merge cells, sheets, and tables
Extract data from cells and ranges
Over 150,000 users

$99 One-time purchase – 14 day free trial

  • Open Excel and open a new sheet or workbook for testing.
  • First, let’s merge our rows without losing data by selecting the cells we want to merge. (1)
  • Then click on “Abletbits Data” in the top menu. (2)
  • Select “Merge Cells.” (3)
  • Then select “Merge Rows into One” if you’re selecting multiple rows. (4)
  • Click the select box next to “Combine with” and select the return key icon. (1)

info Note: You can choose a different separator, but it’s important to note that the data in the merged cells will be combined into a single string, separated by the “Combine with” that you choose.

  • Check the box next to “Merge all areas in the selection.” (2)
  • Then click the “Merge” button. (3)
  • Click on “Extract” in the top menu — a navigation pane will open on the left. (1)
  • Select your merged cell. (2)
  • To extract all the data, select “The first” and enter a number that exceeds the character length of the data within the cell. (3)
  • Then click the “Insert Results” button. (4)

By clicking into the cell, you can see the merged cell’s data has been copied into a new, single cell.

Kutools for Excel

Kutools is another expansion for Excel with over 300 advanced functions, including the option to copy ranges (merged cells). If you’re interested in expanding your Excel arsenal, have a look at what you can do with Kutools.

Over 300 additional tools and options
Copy and compare multiple ranges
Compare worksheets side-by-side
Count and sum cells based on background and font color

$49 One-time purchase – 30 day free trial

  • First, close your Excel application.
  • Then download Kutools.
  • Once installed, open Excel and open a new sheet or workbook for testing.
  • Select multiple cells to merge. (1)
  • Go to “Kutools” in the top menu. (2)
  • Click on “Merge & Split.” (3)
  • Then select “Combine rows…” from the drop-down menu. (4)
  • Select “Combine rows” at the top (or “Combine columns” if merging columns). (1)
  • Select “New line” as the separator. (2)

info Note: You can choose a different separator, but it’s important to note that the data in the merged cells will be combined into a single string, separated by the separator that you choose.

  • Choose to “Merge the combined cells.” (3)
  • Then click the “OK” button. (4)
  • Select your merged cell. (1)
  • Click “Copy Ranges” in the menu. (2)
  • Select “Values” in the “Paste special” options. (3)
  • Then click the “OK” button.
  • Click an empty cell to select a paste location, or type the cell address into the text box.
  • Finally, click the “OK” button.

The data from the merged cell will now be copied into the selected cell. You can also achieve the same effect by clicking on “Exact Copy” in the top menu and following a similar process.


Frequently Asked Questions (FAQ)

How do you unlock merged cells in Excel?

To unlock merged cells in Excel you have to unprotect the sheet or uncheck the “locked” checkbox before protecting the sheet.

  • To unprotect a sheet, click on “Format” in the top menu.
  • Select “Unprotect Sheet” from the drop-down menu.
  • Then enter the password you used to protect the sheet.

  • To leave cells unlocked, select the cells to unlock.
  • Click on “Format” in the top menu.
  • Select “Format Cells” in the drop-down menu.
  • Go to the “Protection” tab.
  • Uncheck the “locked” checkbox.
  • Then go to “Format” again and this time select “Protect sheet.”
  • Enter a memorable password.

Can you mass Unmerge cells in Excel?

Yes, you can mass unmerge cells in Excel by selecting all the cells in your workbook. With all cells selected, click on the drop-down arrow next to “Merge & Center” in the top menu. Then select “Unmerge Cells” from the drop-down menu.


Conclusion

For regular Excel users, the best solution for copying data from merged cells is to use third-party, such as Ablebits or Kutools, as both come with an impressive array of additional features that will make your life easier.

For basic use, double-clicking the cell to copy the raw data, or unmerging the merged cells first, are simple solutions if you can’t copy data from a merged cell.

Thank you for reading.

Saajid Gangat

Saajid Gangat has been a researcher and content writer at Business Tech Planet since 2021. Saajid is a tech-savvy writer with expertise in web and graphic design and has extensive knowledge of Microsoft 365, Adobe, Shopify, WordPress, Wix, Squarespace, and more! You can connect with Saajid on Linkedin.

Recent Posts