How to Merge Cells in Excel

Merging cells can help you organize data across several columns or rows. Sometimes, it can extract and combine the information from separate cells to place them into another row or column. That’s very useful, right? So keep reading because here you will learn how to do it!

Merge and Center

This is a common aid people frequently use, maybe because it’s easy to find in the Home tab. You only have to follow the steps below:

1. Select the cells you want to merge, which can be horizontally or vertically.

Note: You must pick a range of continuous cells; you can’t merge separated ones.

2. Find in the “Alignment” group tools from the “Home” Tab.
3. Click on “Merge & Center.”

Now that you’ve created a single cell, whatever you type into it will be aligned at the center, but don’t worry. You can change the settings later. 

Despite the above, and Before merging the cells, if you have data in each cell you want to join, Excel will show you an error message saying: “Merging cells only keeps the upper-left value and discards other values.” This means you must be careful because Excel will merge the information but delete all you have on them, excluding the first and leftmost content.

Center Across Selection

Even though the “Merge and center” command is easy to use, the “Center Across Selection” is an excellent alternative to avoid some limitations you might find with the first technique, like sorting data and highlighting the cells without inconvenience.

In contrast with the first method, this command is not as simple to find in your worksheet, but don’t worry, to access it, you need to do the following:

1. First, select the cells you want to merge.
2. Hit Ctrl +1 on your keyboard to open the “Format Cells” dialog box.
3. Switch to the “Alignment” tab (Which is the second one).
4. Select “Center Across Selection” from the “Horizontal” dropdown.
5. Click “OK.”

Something great about using this operation is that you can sort and highlight your text more efficiently, just as expected. In addition, if there is any data within the selected cells, you won’t lose the information you have. Instead, it will appear as if they haven't been merged.

Combine Data into one cell

Alright, maybe you need something more specific, and the “Merging” operations above are not enough for you; instead, you need to join values more accurately or in a particular way. For that kind of situation, you can use three incredible techniques to combine two or more data strings into one:

• Concatenation Operator:

This is a trick maybe you didn’t know until reading this article, but when you use the ampersand symbol (&) between values, you can join them in a string like using a formula using the following steps:

1. Select the cell you want the combined data from.
2. In the formula bar, type the equal sign (=).
3. Separate earache value (name cells) with the ampersand (&) symbol.

Let’s make an example: You want to merge the information from A1: that says “Jane” and B1, which correspond to the last name “Doe” Then, in the C1 cell, you need to type: =A1&B1 so that the result will be “JaneDoe.” However, if you want each value separately, you need to enter a space between double quotes so that the formula will be =A1&” “&B1. So the result will be “Jane Doe.”

Using the ampersand symbol as a concatenation operator can be a convenient and easy method to combine data, especially when having separated information in several rows or columns, because you can combine data from different cells even if they’re not continuous.

• CONCAT function:

This is another formula you can use to combine your data and can be quicker than using the ampersand symbol as a concatenation operator. This method is advantageous, particularly when having a lot of data in three or more columns or cells. You only have to select the cell you want the combined data from and type the following syntaxis:

=CONCAT(text1,Text2,text3…) or =CONTACT(Text1:Text3). The last one helps you when working with ranges.

Example: =CONTACT(A1:B1) “JaneDoe”

You have to be careful with this one because, in this case, it won't separate the string data if you have space in the central data cells. But if you require adding extra characters, you must enter them within double quotes and separate them with commas.

Example: =CONACT(A1,”-”,B1) “Jane-Doe”

• TEXTJOIN function:

If the last two functions were helpful, the TEXTJOIN operation would be revolutionary. This is even more efficient than the CONCAT since it allows you to establish repetitive delimiters according to the following syntaxis quickly:

=TEXTJOIN(“delimiter”,ignore_empty,Text1:Text2…)

Let’s explain each code:

• Delimiter: As in the previous technique, this corresponds to the extra character you want to use to cut or separate the string data, which can be a space or another symbol
• Ignore_empty: it’s a setting that tells Excel what to do in case of having empty cells within the selected range.
  • If set to “TRUE,” it will ignore empty cells.
  • If set to “FALSE,” there will be consecutive delimiters with no values in between.
• Text1/Text2: the name of the cell that corresponds to the column letter and the row number.

Example: =TEXTJOIN(“-”,TRUE,A1:B1) “Jane-Doe.”

There you have it! Now merging and combining data can be more straightforward every time, so try these methods and find which can fit you better!