Tag: merge cells excel

There are many ways through which you can do merging cells
in excel. Let’s start this simple step by step guide.

Combine excel cells using Merge & Center option

This is one the easiest way to combine excel cells, this
step take less than a minute to give you results.

Merge & Center is available in Home Tab section. Most of
the Excel users use this option as they find it easy to perform when merging
cells in excel.

There is only one issue that I find while using the Merge
& Center option that It only merge the cells but not the text that is
available in those cells.

Let’s assume that we have a small data set, Btw I love GOT so I used my favorite characters name in my example.

Now If I select cell A1 and B1 and use the option of Merge &
Center then it will keep the data from left most cell (in our example it will
be cell A1) and remove all the data from other cells.

When you perform the cell merging action then excel will
show you a pop up which gives a small warning about data and letting you know
the about the outcome. Just like shown below.

Once you click the Ok button shown on popup, then it will
start merging the data as in our case A1, B1 data and give you the final result
by keeping the data from leftmost cell.

Merge Cells in Excel without losing data:

In the above method we use the simplest method of excel cell
merge but now we will use the different approach and it will keep all our data
as well.

CONCATENATE Formula will help you in merging excel cell and
keep the data as well.

Let’s try this formula for my Favorite GOT data set 😛

Now we are combining the cells A1 and B1 and keeping a space
character as the separator. If you don’t want any separator, then leave it as
it is and use the formula =CONCATENATE(A1,B1)

You have the option to choose comma, semicolon options to
choose as separator.

You can also use ampersand sign to merge the excel cell text,
here is the formula for that =A1&” “&B1

Merge & Center Alternative

There is an alternative of Merge & Center option which
you can use to merge your excel cell, you can use this option if you want to
show merged cells in different column. This option called as 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 option won’t show you any error or warning message.

Why I not choose Merge
& Center option

When you use the merge & center option in merge cell
task then you will find that you are no longer able to sort the data set and
still if you try to sort the data set which have merged cells then it will give
a pop up like below image.

These are the some of the ways through which you can merge the excel data set. Choose the best way as per your needs and requirements and don’t forget that when you choose Center to Across to work option, your leftmost cell must have some data.

About Deepak Jha

I am a Marketer & passionate Excel practitioner. I am using MS excel from past 6 years in my daily routine work, I have tried many things to automate my excel work so i thought why not I help others to automate their work through excel so I started You Learn Excel blog. Just to make things easier for others.