Reporting & Analysis
Feb 9, 2022
TLDR
Merging cells in Google Sheets is a handy feature for combining cells horizontally, vertically, or both, often for titles or content. To merge cells, select them, go to Format, and choose Merge cells. For unmerging, follow the same steps. To merge data from multiple cells, you can use CONCAT, Ampersand (&), or CONCATENATE. CONCAT handles two values, Ampersand offers more flexibility, and CONCATENATE is the most versatile, allowing multiple arguments, delimiters, and data ranges. However, be cautious with merging cells and data as it limits certain operations and formatting options.
Merging cells is a very handy tool in google sheets. It allows you to combine two or more cells horizontally, vertically, or both. One of the main reasons people merge cells is to extend a title or any content across multiple cells.
How do we merge cells in google sheets?
It is very easy to merge cells in google sheets.
First, open google sheets.
Then select the cells you want to merge.
Go to Format → Merge cells
Now click on the kind of merge you want from the options—Merge all, Merge horizontally and Merge vertically. Depending on your selection of the cells, you’ll be able to see different kinds of options.
If you want to unmerge the cells, then you can follow the same steps (Format → Merge cells) and finally click on the Unmerge option.
Another way you can merge cells is by simply clicking the merge button on the toolbar as shown below.
Now you have learned how to merge two or more than two cells in Google Sheets. However, this option does not work if you want to merge data from multiple cells. As you can see below, after you merge cells, you’ll only be able to see the data in the left or top cell.
Now let’s take a look at how you can merge data from multiple cells.
How do we merge data from multiple cells into the same cell in google sheets?
There are different ways in which you can merge your data into one cell in google sheets. This blog explores three of these ways.
CONCAT function:
First, open the spreadsheet in which you want to merge data.
Go to a new cell where you want to add the merged data.
Now type the following formula.
=CONCAT(A1, B1)
Here A1 and B1 indicate the value/data of the cells you want to merge.
Press Enter.
Now let’s try the same with the values from three cells.
Unfortunately, the concat function only accepts two arguments. For you to merge the values of all three cells, you’d have to use the following formula.
=CONCAT(CONCAT(A1, B1), C1)
If you see the final result, you’ll see a merged string without any delimiter. You can add a space in between the two values for a better and cleaner outcome. To do so, you can use the following formula.
=CONCAT(CONCAT(CONCAT(A1, " "), CONCAT(B1, " ")), C1)
Drawbacks of CONCAT:
Using the CONCAT function is a very simple process. However, it has some drawbacks.
It does not allow you to take more than two arguments. Hence, it can only join 2 values at a time.
It only works for values in a single cell. It does not work if you try to combine data ranges.
Concatenation using Ampersand (&):
An alternative to the CONCAT function is Ampersand (&) character. Instead of using ‘CONCAT’ in the formulas, you can simply use ‘&’ to merge values of more than two cells. Using ampersand is much easier and more convenient than using the CONCAT function.
Follow the same steps as above. Now instead of using CONCAT, use the following formula.
=A1&B1
Here A1 and B1 indicate the value/data of the cells you want to merge. You can use this operator to join values from more than two cells without any hassle as follows.
=A1&B1&C1
You can add various other characters and delimiters while using ampersand.
=A1&" "&B1&","&C1
Drawbacks of Ampersand (&):
Ampersand(&) solves the problem of joining more than two values at the same time. However, it also has some drawbacks.
It is not the most convenient option if you have to merge a large number of cells as you’d have to manually type in the formula without any typo.
It does not allow you to combine data ranges.
Improve your DTC game. Sign up for weekly tips.
CONCATENATE function:
Another function that allows you to merge data from multiple cells in CONCATENATE. This function allows you to do more than the CONCAT function and Ampersand(&).
In order to use this function, follow the first two steps from the CONCAT function section. Now on the third step, replace the given formula with the following formula.
=CONCATENATE(A1,B1)
This function takes more than two arguments. Therefore, you can also combine values from more than two cells using this function.
=CONCATENATE(A1,B1,C1)
Similarly, you can add any delimiters and characters (except for line break) of your liking, and use data ranges with this function easily.
=CONCATENATE(A1," ", "is located in ", C1)
=CONCATENATE(A1:C1, A2)
You can also concatenate values with a line break. The formula for line break is slightly different. We use =CHAR(10) for line break in google sheets. So in order to use line breaks with a concatenate function, you can use the following formula.
=CONCATENATE(A1,CHAR(10), B1)
Drawbacks of CONCATENATE:
CONCATENATE seems to be the most convenient option among the three functions mentioned here. Having said that, it also has its own limitations. To name a few,
If you decide to insert more columns to the sheet after applying the formula, you'll have to manually edit every range in your formula.
It has a limitation to join only 30 arguments at a single time.
This function also has a limit of 255 characters for concatenated strings. Any additional characters are omitted.
When to avoid merging cells and data?
Merging cells:
While merging cells can be quite handy, you’d still have to be really careful while using it. The following are some consequences of merging cells.
Once you merge your cells, you won’t be able to perform sorting and filtering operations, copy-paste or move data into the merged cell, and even carry out any analysis that would require you to select the columns containing the merged cells.
Merging data:
Here are some of the cautionary steps while merging data:
It’s important to remember that while merging data, you’ll also lose the format of the data. Therefore, if you require your merged data to have some format then you’d have to state the format explicitly in the formula. This can be achieved by using the TEXT function.
You cannot use the merging functions with the QUERY function.
Sona Maharjan
About the Author