How to Merge Two Cells in Excel Without Losing Data (2 Ways)

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.

dataset to merge cells with data

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

select cells to merge

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

merge two cells in excel using Fill Justify

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

merged data from two cells

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

merge two cells in excel


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.

insert module in VB editor

  • 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

code to merge two cells in excel

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

add custom group to ribbon

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

click rename

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

rename custom group

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

add macro to merge tow cells in excel

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

change macro button name

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

macro in ribbon to merge two cells in excel

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

dataset to merge two rows of cells in excel

  • Then enter the following formula in cell B9. Next drag the Fill Handle icon to the right to see the following result.
=B5&","&B6&","&B7

merge rows using ampersand (&)


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.

dataset to merge multiple columns

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

enter data in the required format

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

merge two columns of cells in excel


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.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

6 Comments
  1. Reply
    Melinda Calabrese Aug 31, 2018 at 9:35 PM

    Oops! I think you may have meant to say “Choose the entire column” in text shown below.

    Choose the entire row either using the mouse or using this keyboard shortcut (CTRL + SHIFT + Down Arrow) when your cell pointer is placed at the first cell of the column.

    Thanks for the tips

  2. Thanks so much Kawser. Will definitely try the new methods. I knew of only the concatenate function previously.

  3. Hi, Dear

    Many many Thanks for making my life easier And Inshallah ALLAH will pay you.

    You are really helping a lot. Be Blessed

    • Reply
      Osman Goni Ridwan Aug 30, 2022 at 1:52 PM

      Thank you! Shakeel. We are really honored to hear that our articles have helped you. Browse articles of Exceldemy more and share with your friends and family and keep supporting us!

Leave a reply

ExcelDemy
Logo