How to Merge Cells in Excel
There are several ways to merge cells in excel, using formula or using no formula, losing data or losing no data. Which solution is right for you, please read the article below.
Solution #1, Using Ribbon Menu
The Merge Cells menu is in the “Home” tab and “Alignment” group. There are several options to choose from.
Merge & Center
Merge the selected cells into a single combined cell and make a center alignment.
Merge Across
Merge the selected cells into a combined cell for the same row
Merge Across is a Merge Cell for several rows in one command, there is no difference in results between Merge Across and Merge Cell if the selected cells consist of one row
Merge Cells
Merge selected cells into a single combined cell without alignment.
Unmerge Cells
Unmerge Cells returns the merged cells into the original form.
Unmerge Cells only returns the cell to its original form, not the data.
Merge Cells Limitation
Merge Cells only merge the cells, not the data. Excel only keeps the upper-left values and discard other values. No commands can restore the discarded data, including unmerge cells
Solution #2, Using Right Click Menu
You can merge cells with the right-click menu, then select Format cell, a dialog appears as shown below.
Check the Merge Cells check box in the Text control section. The other setup is optional. You can make text alignment or text orientation settings, all in one dialog.
If you only check the Merge cells checkbox, the results are like the Merge Cells option on the Ribbon menu.
Solution #3, Using Ampersand “&” Sign
The Formula
=A2&" "&B2&" "&C2
The formulas merge the data in several columns into one column without losing data. The formula merges the data, not the cells.
Keep in mind!!
The new column as a result of combining several columns containing a formula, if you copy the formula and paste elsewhere could give the different results.
To avoid the different result, make sure you do a
Copy – Paste Special – Values for all data in the combined column.
Please read the article below for detailed information about Paste Special.
Solution #4, Using CONCATENATE Function
The Formula
=CONCATENATE(A2," ",B2," ",C2)
The Formula writing for the CONCATENATE function and the ampersand sign requires you to write all the columns to be merged, along with the separator.
If there are many columns to be merged will create a long formula. For a simpler solution, use the TEXTJOIN function.
Solution #5, Using TEXTJOIN Function
The Formula
=TEXTJOIN(" ",TRUE,A2:C2)
The TEXTJOIN function allows you to merge cells using a range, without having to write one by one of the cells to be merged. Also, you only write the separator once, unlike the CONCATENATE function you must write the separator repeatedly.
Solution #6, Using the Magic Way
No formula, losing no data
Which One is the Best?
Solution #1 and #2 only merge the cells, not the data inside. As a result, you will lose some data.
Solution #3 and #4 are using the formula, losing no data because the merged cell is done by putting it in a new column. The disadvantage, you must write all the column addresses to be merged one by one as well as the separator.
Solution #5 using a formula, you only write the column range address and the separator once. But my choice is solution #6, simple, no formula, losing no data and magic 😊.
What about your choice?
Related Function
Function used in this article
Usefull links
- Microsoft Excel MERGE and UNMERGE cells documentation