How to Merge Cells in Google Sheets (4 Ways)

Merging cells is one of the core functions of any spreadsheet application. Not only for organizing data, cell merges can also be utilized to make your worksheet more presentable.

In this article, we will be looking at how to merge cells in Google Sheets. We shall see four distinct approaches along with some tips and challenges that come with merging cells in the Google Sheets application.

Let’s get started.

4 Ways to Merge Cells in Google Sheets

1. Merge Cells from the Toolbar/Menu Bar

The quickest and simplest way to merge cells in Google Sheets is to use the Merge cells option right from the Menu bar or Toolbar.

For our example, we will be using the following worksheet. Here, you can see that the title of the table, Metro City Bookstore, is overflowing into the next cell and is awkwardly placed.

base worksheet for how to merge cells in google sheets

We are going to merge cells B2, C2, D2, and E2 to make one combined cell that will house the title.

To do that, we have to select the respective cells and click on the Merge cells option in the toolbar. The button is located right above the formula bar and to the right.

navigating to the merge cells option in Google Sheets

Our merge result:

merger result of how to merge cells in google sheets

For our finishing touches, let’s center-align the title. Keyboard shortcut: CTRL+SHIFT+E

putting the finishing touches after merging cells

Loss of Data

Let’s move away from our primary worksheet to see and understand one of the glaring limitations of merging cells – the loss of data.

Here we can see four cells containing text values. We will attempt the merge them with the method we have just discussed.

attempting to merge cells with values in them

Unfortunately, we are hit with this message:

warning for merging cells with values in google sheets

If we click OK, our merged result becomes:

lost data after merging cells

As we can see, the selected cells have been successfully merged, but the only value remaining is the text, “eenie”, as was warned by the pop-up message. All the other values have been wiped out.

Unmerge Cells

Continuing from our last example, let’s look at how we can unmerge the cells.

With our merged group of cells selected, navigate back to the Merge cells option. It should now be highlighted green.

You can click on it once to unmerge your selection, or you can select the drop-down menu to find the Unmerge option.

the Unmerge option in Google Sheets

Our result:

cells unmerged

As you can see, the data we lost during the merge does not come back. If you want to merge cell values from different cells into one cell, head on to method 4 of this article.

2. Merge Cells from the Format Tab

Next, we are going to be looking at a somewhat non-traditional way to merge cells, that is by using the Format tab.

We will be presenting this method, and all of its iterations, in the following worksheet.

the format tab with new dataset

I. Merge All

The first merge method we are going to be looking at is the Merge all option.

Format tab > Merge cells > Merge all

merge all from the format tab

The result:

result of merging all cells in google sheets

As you can see, only the top-left cell value remains after the merge, the same as the previous method. So, we can understand that, by default, merging cells in Google Sheets is the Merge all function.

II. Merge Horizontally

Next, we will try out the Merge horizontally option.

merge horizontally in the format tab

With this option, the merge will occur towards the right, keeping the first column intact.

Before merge:

before merge worksheet

After horizontal merge:

horizontal merge applied

III. Merge Vertically

Finally, we will look at the Merge vertically option.

merge vertically from the format tab

Here, the merge will occur towards the bottom of the selection, keeping the first row intact.

Before merge:

before merging

After vertical merge:

after merging cells vertically in google sheets

Note that we can also find these three options and Unmerge from the Toolbar menu (use drop-down).

all merge options are available in from the toolbar

3. Merge Cells from a Mobile Device

In case you didn’t already know, Google Sheets is also a mobile-friendly application. It can be used in both Apple and Android devices.

To merge cells in Google Sheets with a mobile device, follow these steps:

Step 1: Select the cell range you want to merge.

Step 2: Tap on the Merge icon, found at the bottom of the screen.

merge cells option in google sheets on mobile

Like before, if your selection contains values in each cell, a warning pop-up will be presented before you can move further.

merge cells warning on a mobile device

Step 3: Tap OK.

how to merge cells in google sheets in a mobile device

And voila! We have successfully merged our selected cell with a mobile device.

Points to Note

The only limitation of using the mobile app is its lack of merge options. Only the Merge all version is available.

You can tap the Merge icon again to Unmerge.

You can also access the merge option from the Format menu (the big “A” at the top of the screen). Format > Cell > Merge cells

4. How to Merge Cells Without Losing Data in Google Sheets (Combining Cell Values)

So far, all the methods we have seen to merge cells had one glaring limitation – we lost data if we merged multiple cells containing data/values.

Though we can’t remedy the problem directly, we do have a couple of workarounds to this problem.

We will be taking the values from multiple cells and moving them to a separate cell with a few added customizations.

I. Ampersand (&)

Our first method works with the ampersand symbol (&). This symbol is used to combine or concatenate multiple values in a cell together and is used in all spreadsheet applications.

Here we have a worksheet where we look to merge or combine the values in the Name and City columns and put them in the Combined column.

worksheet for how to merge cells in google sheets without losing data

In cell D2, we type in the formula:

=B3&C3

The result:

combined cells with ampersand

Not going to lie, this merge result looks unprofessional. Let’s fix it up a bit by adding some spaces and a hyphen to make it more presentable.

Our new formula:

=B3&" - "&C3

combined cells with ampersand and delimiter

This will help organize our data for the rest of the column.

applying the formula to the rest of the column

Thus, we have successfully merged cells without the loss of data, albeit in a separate position.

II. CONCATENATE Function

An alternative to the previous ampersand method is using the CONCATENATE function.

The syntax:

CONCATENATE(string1, [string2, ...])

The function simply takes two or more string values and puts them together in a single cell.

Our formula:

=CONCATENATE(B3,C3)

using CONCATENATE function to merge cells in google sheets

Similar to our previous method, we can add spaces and other symbols as text (in quotation marks “”) to make the data more presentable.

=CONCATENATE(B3," - ",C3)

cleaning up the CONCATENATE formula result

Issues with Merging Cells

On top of losing data, we still face a few more challenges when we merge cells.

  1. The merged cell is seen as one entity. You cannot select the cells in the merge individually, Google Sheets will automatically select the merged cell as well.
  2. Merged cells will prohibit you from sorting or filtering. If you are looking to sort a column or row that contains a merged cell, you will be hit with an error.
  3. Copying merged cells also copies formatting. You cannot paste values of a merged cell separately, the formatting will also be carried over.

Final Words

We hope that all the methods we have discussed to merge cells in Google Sheets will come in handy for your spreadsheets. Feel free to leave a comment with any queries or advice you may have.


Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo