Have you ever tried to merge cells in 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.
Read More Excel Articles to Upgrade Your Knowledge