How do I merge two cells in Excel without deleting data?

How to Merge Cells in Excel Without Losing Data (Step-by-Step)

• -- By Sumit Bansal

FREE EXCEL TIPS EBOOK - Click here to get your copy

There are various ways you can merge cells in Excel.

One of the most used ways is using the Merge & Center option in the Home tab.

The issue with using Merge & Center is that it can merge the cells, but not the text within these cells (i.e., you lose some data when you merge the cells).

Let’s say we have a data set as shown below:

If I select cell A1 and B1 and use the Merge & Center option, it will keep the text from the left-most cell (A1 in this case) and but you will lose the data from all other cells.

Excel is not completely ruthless though – it warns you before this happens.

If you try and merge cells which have text in it, it shows a warning pop-up letting you know of this (as shown below).

If you go ahead and press OK, it will merge the two cells and keep the text from the leftmost cell only. In the above example, it will merge A1 and B1 and will show the text John only.

This Tutorial Covers:

• Merge Cells in Excel Without Losing the Data
• The Benefit of Not Merging Cells in Excel
• Alternative to Using Merge & Center

Merge Cells in Excel Without Losing the Data

If you don’t want to lose the text in from cells getting merged, use the CONCATENATE formula. For example, in the above case, enter the following formula in cell C1: =CONCATENATE(A1,” “,B1)

Here we are combining the cells A1 and B1 and have a space character as the separator. If you don’t want any separator, you can simply leave it out and use the formula =CONCATENATE(A1,B1).

Alternatively, you can use any other separator such as comma or semi-colon.

This result of the CONCATENATE function is in a different cell (in C1). So you may want to copy it (as values) in the cell which you wanted to merge.

You can also use the ampersand sign to combine text. For example, you can also use =A1&” “&B1

The Benefit of Not Merging Cells in Excel

When you use Merge & Center option to merge cells, it robs you of the ability to sort that data set. If you try and sort a data set that has any merged cells, it will show you a pop-up as shown below:

Alternative to Using Merge & Center

If you want to merge cells in different columns in a single row, here is an alternative of Merge & Center – the Center Across Selection option.

Here is how to use it:

• Select the cells that you want to merge.
• Press Control + 1 to open the format cells dialogue box.
• In the Alignment tab, in the Horizontal drop-down, select Center Across Selection.

• Click OK.

This would merge the cells in a way that whatever you enter in the leftmost cell gets centered, however, you can still select each cell individually. This also does not show an error when you try and sort the data.

NOTE: For Center to Across to work, make sure only the leftmost cell has data.

You May Also Like the Following Excel Tutorials:

• How to Unmerge Cells in Excel (3 Easy Ways + Shortcut)
• CONCATENATE Excel Range (with and without separator).
• How to Find Merged Cells in Excel.
• How to Combine Cells in Excel.
• How to Combine Multiple Workbooks into One Excel Workbook.

18 thoughts on “How to Merge Cells in Excel Without Losing Data (Step-by-Step)”

1. Kumaresh chandro ghosh

   May 2020 at 7:37 am

   Great jobs sir

2. George Georgiou

   August 2019 at 4:43 pm

   Hi, I am trying to merge two cells (a4 & a5) into another worksheet and for the formula to copy down (i.e. cell a1 has merged a4/a5, a2 has merged b4/b5, a3 has merged c4/d4 etch

3. mIKE

   September 2018 at 7:06 pm

   I AM TRYING TO MERGE AREA CODES FROM ONE COLUMN AND PHONE NUMBER IN ANOTHER AND THEN BE ABLE TO DELETE THE TWO COLUMNS I DONT NEED ! HOW DO I SAVE TH INFORMATION IN CELL 3?

   • pius

     October 2019 at 3:47 am

     Copy merged information from the third column, paste special as values into the 4th column. Now you can delete columns 1,2 and 3.

4. Tais Malheiro

   July 2016 at 9:53 pm

   I’m a beginner in Concatenate formula. I need a help to make a line break or change line when concatenate the two cells. Like put the content of the cells not to side by side, but above/under inside the same cell. There is a way to do that?

   • jim

     August 2016 at 4:38 pm

     yeah, add a carriage return character, which would look like this:
     =CONCATENATE(cellref1,CHAR(10),cellref2)
     or
     =cellref1&CHAR(10)&cellref2

5. Rushabh Shah

   October 2015 at 9:38 am

   This is just brilliant! Too good! Going to use this everyday now!

   • Sumit Bansal

     October 2015 at 10:37 am

     Thanks for commenting Rushabh.. Glad you found this useful 🙂

6. Rudra Sharma

   October 2015 at 10:18 pm

   hi Sumit,

   what is this “video like” screenshot called and also how to make it?
   Regards Rudra

   • Sumit Bansal

     October 2015 at 10:36 am

     Hello Rudra.. Its a GIF image.. I use Camtasia to make it

7. GStroeder

   October 2015 at 8:25 pm

   The Center Across Selection button/icon can be directly added to the QAT.
   QAT – (click drop down) select More Commands – Choose Commands from: All Commands – scroll to Center (Not Merge) Across Selection. Select and add to your QAT.

   • Sumit Bansal

     October 2015 at 9:13 pm

     Thanks for dropping by and commenting.. The Center Icon from the list would centre the content in the cells but would not Center Across Selection. Unfortunately, there is no option in that list that can do that. The only way is to create a macro and add it in QAT.

     • GStroeder

       October 2015 at 10:43 pm

       Excel 2010 (ver 14.0, 64 bit). In my Customize QAT, there is Center (that you refer to) and right below it, I have “Center (Not Merge) Across Selection”. It does exactly that.

8. Izabela

   October 2015 at 8:13 pm

   What if I would like to merge cells in different rows in a single column?

   • Sumit Bansal

     October 2015 at 9:14 pm

     Thanks for commenting Izabela.. In case of rows, there is no other way. You need to use Merge & Center. The drawback is that it will keep the content of the top cell only.

     • Izabela

       October 2015 at 12:02 pm

       Thank you! I love your articles! They are so useful!

9. indzara

   October 2015 at 7:39 am

   Good post. Thanks for sharing. I use ‘Center Across selection’ sometimes. I wish that it was easier to apply it without having to go through format dialog box. 🙂

   Can I merge cells in Excel and keep all data?

   With the Merge Cells add-in, you can combine data from several cells using any separator you like (e.g. space, comma, carriage return or line break). You can join values row by row, column by column or merge data from the selected cells into one without losing it.

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

   Merge Cells in Excel Without Losing the Data If you don't want any separator, you can simply leave it out and use the formula =CONCATENATE(A1,B1). Alternatively, you can use any other separator such as comma or semi-colon. This result of the CONCATENATE function is in a different cell (in C1).

   How to merge two cells in Excel?

   Merge cells.

   Click the first cell and press Shift while you click the last cell in the range you want to merge. Important: Make sure only one of the cells in the range has data..

   Click Home > Merge & Center..

   How do I merge cells in sheets without losing data?

   How to merge cells in Google Sheets without losing the data?.

   Choose what to combine: columns, rows, or cells..

   Pick the delimiter to separate values..

   Decide where to place the results..

   Tick off the additional options..

   Click Merge..