How to merge cell in excel

How to Merge Cells in Excel (Easy Step-by-Step Guide)

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. 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 leftmost cell (A1 in this case) and remove the text 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 the Right Way

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.