Merge Cells Without Losing Data in Excel

When working in Excel, you may need to merge cells to improve data presentation, such as centering the table title at the top of the table.

To merge or combine cells in Excel, you can use the ‘Merge & Center,’ ‘Merge Across,’ and ‘Merge Cells’ merging options, which you can access by clicking the down arrow on the ‘Merge & Center’ split button in the Alignment group of the Home tab.

Note: The ‘Merge & Center’ option, usually the default option, merges the selected cells and centers any retained content in the combined cell. The ‘Merge Across’ option combines the selected cells in the same row into one large cell. The ‘Merge Cells’ option combines the selected cells and leaves the retained data in the merged cell without changing its alignment.

Table of Contents

What Happens When You Merge Cells with Data?

When you merge or combine cells with data, all the data, except in the upper-left cell, is deleted. Let me give you an example to illustrate this fact.

Suppose you have the dataset below and want to merge cells A2 and B2.

Merge Cells Without Losing Data in Excel dataset

You can use the steps below:

Select cells A2 and B2.

Open the Home tab and click the ‘Merge & Center’ button on the Alignment group.

Excel displays a warning message indicating that merging cells only keeps the upper-left value and discards other values.

Click OK.

The steps above merge cells A2 and B2 and delete the data in cell B2, leaving only the data in cell A2, as shown below:

Also read: How to Unmerge All Cells in Excel?

Merge Cells Without Losing Data

Can we work around Excel’s limitation and retain all the data in merged cells? Yes, let me shows you how.

Method #1: Use Concatenation Functions

You can use the CONCAT or TEXTJOIN functions or the ampersand (&) operator to concatenate or join the values of cells in one cell before merging the cells.

Suppose you have the following dataset and want to merge the cells in columns A and B without losing data.

Here’s how to do it:

Use any of the following formulas in column C:

=CONCAT(A2," ",B2)

=A2&" "&B2

=TEXTJOIN(" ",TRUE,A2,B2)

Note: We have combined columns A and B values using the space character (” “) as the separator. You can use other separators, such as commas or semicolons. If you do not want to use any separator, enter the formula as ‘=CONCAT(A2,B2).’

Select the cell range C2:C6 and press CTRL + C to copy the contents.
Select cell A2.
On the Home tab, open the Paste drop-down menu and click the ‘Values’ option in the ‘Paste Values’ section.

The above step pastes the results of the formulas in column C as static values in column A.

Delete the contents of column C.
Select the cell range A2:B6.

On the Home tab, on the Alignment group, click the down arrow on the ‘Merge & Center’ split button and click ‘Merge Across.’

Click OK on the series of message boxes that appear until all the pairs of cells are merged, as shown below:

Merge Cells Without Losing Data in Excel

Method #2: Use the Fill Justify Option

You can use the Fill Justify option in Excel to combine the data in several rows in one column in one cell. This technique does not merge cells, but the results are similar to merged cells.

Let’s say you have the data below in one column and want to combine the data in one cell.

Here’s how to do it:

Select the cells with the data you want to combine.

Make the column wide enough to accommodate the data in one cell.

On the Home tab, on the Editing group, click the down arrow on the Fill icon and click the Justify option.

The above steps move the data in the selected cells to the top-most cell as shown below:

Justify formatting applied to cells in Excel

The combined values may spread to two or more rows if the column is not wide enough to fit all the data in one cell. You can widen the column and repeat the process to ensure all data fits in one cell.

Note: The above technique has the following limitations:

Fill Justify only works within a single column.
It works for text only. It doesn’t work for numerical values or formulas.
It does not work well if empty cells are between the target cells.

Method #3: Use the Excel Clipboard

You can merge data in cells by copying data to the Excel clipboard and pasting it into a cell.

Suppose you have the dataset below and want to merge the data in columns A and B in a single cell.

Here’s how to do it:

Select the cell range A2:B6 and press CTRL + C to copy it.
Click the dialog box launcher on the bottom left of the Clipboard group to open the Clipboard.

The Clipboard pane opens on the left of the Excel window, showing the items you have copied.

Select the cell where you want to paste the data and press F2 to enter edit mode. The cursor will blink inside the cell.
Click the Paste All button on the Clipboard.

The merged data appears in the cell you selected, as shown below:

You can delete the original data and remain with the merged data.

Also read: How to Find Merged Cells in Excel?

An Alternative to Merge & Center in Excel

Merging cells in Excel can help present the data better by, for instance, centering the table title across the top of the table.

However, merging cells has the following adverse effects:

You cannot sort columns that have merged cells.
Flash Fill and AutoFill features cannot work on cell ranges that have merged cells.
You cannot convert a range with merged cells into a table.
Merged cells make accessing or referencing individual data pieces difficult.

If you must merge cells, restrict merging to header rows or areas that do not involve data manipulation.

In this section, I will show you how to utilize the ‘Center Across Selection‘ option, which provides a similar visual effect to merging cells without the adverse effects mentioned above.

Use ‘Center Across Selection’ Option

Suppose you have the following dataset below and want to center the text in cell B3 across the cell range B3:D3.

Here’s how to do it:

Select the cell range B3:D3.

Press CTRL + 1 to open the Format Cells dialog box.
On the Format Cells dialog box, click the Alignment tab, open the Horizontal drop-down menu, and select the Center Across Selection option.