How to Merge Cells in Excel Without Losing Data

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.

How to Merge Cells in Excel - Merge and Center Icon

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:

How to Merge Cells in Excel - Data Set

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

How to Merge Cells in Excel - Pop Up Message

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.

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:

How to Merge Cells in Excel - Sort Error

Also read: Find Merged Cells in Excel

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.

How to Merge Cells in Excel - 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:

Excel Ebook Subscribe


Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

18 thoughts on “How to Merge Cells in Excel Without Losing Data”

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


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

  3. 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?

    • yeah, add a carriage return character, which would look like this:

  4. hi Sumit,

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

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

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

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

  6. 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. 🙂

Comments are closed.