How to merge cells in Excel without losing data in 2 steps

Merge cells in Excel can be as easy as one click. However, how to merge cells in Excel without losing data?

Before discussing how to merge cells in Excel without losing data. Let’s call over how to merge cells in Excel.

How to Merge Cells in Excel

As mentioned, merge cells in Excel cab be as easy as one click. And this how usually we will use is to click the Merge & Centre button under the Home Ribbon

Merge & Center button

Assuming we are going to merge the cells A1 and B2, following is the steps:

  1. Select both A1 an A2
  2. Click “Merge & Center”

The cells A1 an dA2 will then be merged

However, what if A1 and B2 is not an empty cell?

Assuming following is the value for A1 and B1

RowAB
1AB

Let’s have a look.

Merge Cell & Center with warning

Excel will simply populate a warning indicate that only the upper-left cells will be keep if both A1 and B1 is filled. This means that the value in B1 will then be lost.

How to easily merge cells in Excel without losing data

Basically, the idea to do this is firstly combine the value of both cells in a different cells using either the Concatenate function and follow by merging the cell.

The CONCATENATE function takes two or more arguments which it combines

Following is the syntax of the function

CONCATENATE(text1, [text2], …)

Note: In Excel 2016, user should use “Concat” function with the same syntax.

For example. we may use cell A3 to combine the value of A1 and B1.

The formula in A3 will be

=concatenate(A1,B1)

Now cells A3 will have the combined value between A1 and B1, you can now merge the cell A3 with any other cells. AS long as A3 is on the top left corner of the new range, the formula will now be maintained.

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

Concatenate Vs Merge Cells

So what is the difference between using the Concatenate function against merging a cell?

The result is very obvious that the concatenate function or the ampersand sign “&” is to to product a result that combining multiple values. However, Merge Cells is to use the content on the top left corner of the range to cover the entire range into one single cells.

So in a simple word, concatenate is on data level while Merge Cell is more for presentation only

Disadvantage of using merge cells function in Excel

You are not able to sort your table or range if it have a merged cell, unless it is the column title.

For example we have a table like thig

Table with merged cells

You are expected to see following error message when sort it

To do this, all the merged cells need to be the same size
Error while sorting merged cells.

Should I use Merge Cells function?

You should only use Merge Cells function when ever is needed.

Basically merge cell function is more for presentation purpose, if you want to merge cells across multiple rows, then Merge Cell is your only option.

However, if you are simply going to merge all cells in the same rows, You can simply set cell alignment to central alignment as an alternative option

Center Across Selection Alignment – Alternative option to Merge Cells

You can use Merge & Center – the Center Across Selection option as an alternative to merge cells in different columns in a single row.

Here is how to use it:

  • Select the cells that you want to merge.
  • Click Alignment Setting under the Alignment session in the home Ribbon
  • In the Alignment tab, in the Horizontal drop-down, select Centre Across Selection.
  • Click OK.

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

1 thought on “How to merge cells in Excel without losing data in 2 steps”

Leave a Comment