How to merge cells in google sheets without losing data – without plugins

I found myself in the need of merging two adjacent cells in google sheets without losing the data contained in them. I searched online and found everyone suggesting to download an addon. But then you discover the addon is not completely free, and I don’t want to give access to my data to an addon only to make a simple task. So I found an alternative method to merge two cells joining the content.

Avoid joining cells if you have formulas inside or referring to them

When we join two cells, we are forcing the spreadsheet’s rigid structure made of ordered and addressable rows and columns. This is not a great idea for cells containing data, as this can mess up selections, formulas, ordering, and filters. For these reasons, it’s better to use the merge function for “cosmetic” applications (titles, notes, etc.) and not in the data tables.

Creating a new temporary column

The first step is creating a new column we will use to temporarily merge the cell contents. This will be removed after the merge, so it’s not important where you place it. You could even put it on the first column of the sheet, but that would be more difficult to find later. I prefer to add it to the left or right of the cells I need to merge.

In our example, we will add it to the left as column A. 

To do this we just need to right-click on the left cell and choose “Insert 1 column left”.

google sheets insert column left example

Merging the contents of the cells

Now we have to merge the two (or more) cells we want to merge.

Here it depends on what you want as a result. We have to write a function that merges the cell content as needed. You can concatenate the text of the individual cells, or if you have numeric values maybe you want the sum in the merged cell.

Whatever you want, you have to write a formula to get the result in the new column.

In our example, we will concatenate the values with a space character as a separator in the middle. A simple formula to merge values is the following one:

=CONCATENATE(B5;" ";C5)

As you can see, the formula asks Google Sheets to concatenate the B5 cell’s content, our space separator, and the C5 content. You can use a different delimiter for the merge, such as a comma, or a comma and a space.

If instead you have numerical values and want to sum them, then you need to write a simple formula with the SUM function.

=SUM(B8:C8)

sum function google sheets

If you have to do the join on multiple rows, then you can just drag the formula to replicate it.

Copy the merged cell values to the top-left cell

Now that we have all the data in the temporary cell, we have to copy it to the top-leftmost cell of the cells we want to merge. So in our example, we have to copy everything on the B column as we’re going to join the B5 and C5 cells.

Since the value we’re copying is a formula, and it’s involving the destination cell, we can’t just copy and paste from the helper column to the destination cell. The right way is to use the “Paste Special” -> “Values Only” option. This will paste the value instead of the formula. If you like keyboard shortcuts, you can use the Ctrl + Shift +v for Windows, and Command + Shift + v for Mac.

Once you pasted the content, the temporary cell will update the formula result and give a wrong value, but we don’t need it anymore.

merged value into the left most cell

Merge cells into a single cell

Now that we have all the data in the right place we can proceed to merge the cells.

Google Sheets offers a simple way to merge the cells: You just need to select the range of cells you want to merge and then click on the “Merge Cells” button.

The default behavior of this operation is to keep only the content of the top-left cell, so if you have data in other cells that are being merged, you’ll get a warning from Google Sheets saying that you’re going to lose some data. Our data is in the right place, so we can ignore this message.

google sheets warns you if you're merging cells with data in them

Once we confirm, the merge command will be executed, and the selection will become a single cell containing the right data.

Remove the temporary column

Now that everything is merged, we have to get rid of the temporary column.

You can select the entire column by clicking on it and then right-click and choose the “Delete column” option. Alternatively, you can select a single cell in that column, right-click on it, and use the “Delete column” from there.

the final result with the merged cell containing the merged values

This article is part of our productivity tips for Google Sheets series. You can find them all on our Tips and tricks for Google Sheets page.

How to merge two cells on the same column?

In our example, we merged in a row, but this method works in a column too, just create a temporary row instead of a column, and copy the merged values to the top of the array of cells you’re merging.

Conclusions

OK, it seems a long method, we all would like a merge feature from google sheets, but once you get familiar with it, it’s quicker than installing an add-on for just a simple feature.