How do I merge two cells in Excel and keep both data?

This article shows how to merge two cells in excel without losing data. Excel allows us to merge cells to create labels or headings and organize the data in a more presentable manner. Usually, Excel is used to store data and not to present them. But a dataset needs labels and headers if it is used by multiple users so that they can understand the dataset easily. Therefore, the Merge & Center feature in excel is very useful to merge multiple cells.

Unfortunately, the Merge & Center feature only keeps the first cell data after the cells are merged. This may become very inconvenient if you often need to merge cells containing data as you will lose all data except the first cell. So we will show you 2 ways to merge cells in excel without losing any data.

Download Practice Workbook

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

2 Ways to Merge Two/Multiple Cells in Excel Without Losing Data

Here are the 2 ways to merge two cells in excel without losing data.

1. Use Fill Justify Feature to Merge Two Adjacent Cells in a Column

Follow the steps below to merge two or more adjacent cells in a column using the Fill Justify feature in Excel.

📌 Steps:

• First, consider the following mock dataset containing the quarterly sales made by employees in 2022. Here the first and last names of the employees are in adjacent cells in the same column. Now you want to merge the cells containing the first and last names. You can only use this feature when the dataset is formatted this way.

• So, first, select the cells and increase the column width so that the contents of both cells can fit into one cell.

• Then, select Fill >> Justify from the Editing group in the Home tab.

• After that, you will see data from the two cells are merged together separated by a space in the first cell.

• Now you can select the two cells and merge them together to get the desired result.

2. Create a Ribbon Feature Using a VBA Code to Merge Two or Multiple Cells Without Losing Data

Follow the steps below to create a ribbon feature similar to Marge & Center using VBA to merge two cells without losing data.

📌 Steps:

• First, save the workbook as a macro-enabled workbook. Then press ALT + F11 or select Developer >> Visual Basic to open the VB editor. Next select Insert >> Module.

• Next, copy the following code and paste it onto the blank code module.

Sub MergeCellsWithData()
Application.DisplayAlerts = False

Dim Value As String
Dim Range As Range
Set Range = Selection

If Range.Rows.Count = ActiveSheet.Rows.Count Then
MsgBox "Please do not select an entire column(s).", Title:="Merge Cells With Values"
Exit Sub
ElseIf Range.Columns.Count = ActiveSheet.Columns.Count Then
MsgBox "Please do not select an entire row(s).", Title:="Merge Cells With Values"
Exit Sub
End If

For Each cell In Range
Value = Value & " " & cell.Value
Next cell

If MsgBox("Cell values will be merged seperated by space(s). Are you sure?" _
, vbOKCancel, Title:="Merge Cells Without Losing Data") = vbCancel Then Exit Sub

With Range
.Merge
.Value = Application.WorksheetFunction.Trim(Value)
.HorizontalAlignment = xlCenterAcrossSelection
End With

Application.DisplayAlerts = True
End Sub

• Now press ALT + F + T or select File >> Options to access the Customize Ribbon Alternatively, you can right-click on the ribbon to do that. Then go to the Customize Ribbon tab. Next, select the Alignment group below the Main Tabs. Then, click on New Group.

• After that, a new custom group will be added. Click on Rename to change the group name.

• Next, choose a symbol, enter a Display Name and, click OK.

• Now select Macros from the “Choose commands from” dropdown and choose the macro name that matches the subroutine procedure. Then select Add to add the macro as a feature in the ribbon. Next, click Rename.

• Then choose a symbol, enter a Display Name, click OK and, OK again. After that, a macro button will be added to the ribbon.

• Now, select the cells to merge and click on the macro button in the ribbon.

• After that, you will see a confirmation message. Click OK to get the same result as obtained in the first method.

How to Combine Two/Multiple Rows in Excel Without Losing Data

You can use the Ampersand symbol to combine two/multiple cells in excel. Follow the steps below to apply it to combine multiple rows.

📌 Steps:

• First, consider the following example dataset. Assume you need to combine the rows to get each food category in a single cell.

• Then enter the following formula in cell B9. Next drag the Fill Handle icon to the right to see the following result.

How to Combine Two/Multiple Columns in Excel Without Losing Data

You can apply the Flash Fill feature in excel to combine multiple columns without losing any data. Follow the steps below to be able to do that.

📌 Steps:

• First assume you need to combine the First_Name and the Last_Name columns to create the Full_Name column as shown in the following dataset.

• Then, type the first and last names from the first two columns in cell D5 as shown below. If you need the data from the columns separated by commas, put a comma between them instead of a space.

• Finally, press CTRL + E to merge the columns as shown below. You can also access the feature from the Editing group in the Home tab.

Things to Remember

• You must select adjacent cells in the same column only to use the Fill Justify feature.
• The Fill Justify feature won’t work if you don’t increase the column width as much as needed to fit all data from the selected cells into the top cell in the selection.
• You can also use the CONCATENATE, CONCAT, and TEXTJOIN functions in Excel to combine multiple rows or columns without losing any data.

Conclusion

Now you know how to merge two cells in excel without losing data. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.