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.
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:
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.
- 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:
18 thoughts on “How to Merge Cells in Excel Without Losing Data”
Great jobs sir
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
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?
Copy merged information from the third column, paste special as values into the 4th column. Now you can delete columns 1,2 and 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:
=CONCATENATE(cellref1,CHAR(10),cellref2)
or
=cellref1&CHAR(10)&cellref2
This is just brilliant! Too good! Going to use this everyday now!
Thanks for commenting Rushabh.. Glad you found this useful 🙂
hi Sumit,
what is this “video like” screenshot called and also how to make it?
Regards Rudra
Hello Rudra.. Its a GIF image.. I use Camtasia to make it
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.
What if I would like to merge cells in different rows in a single column?
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.
Thank you! I love your articles! They are so useful!
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. 🙂
I wish the same. The only option I can think of is to write a macro and have it in the QAT.