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.
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.
Our merge result:
For our finishing touches, let’s center-align the title. Keyboard shortcut: CTRL+SHIFT+E
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.
Unfortunately, we are hit with this message:
If we click OK, our merged result becomes:
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.
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.
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.
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
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.
With this option, the merge will occur towards the right, keeping the first column intact.
After horizontal merge:
III. Merge Vertically
Finally, we will look at the Merge vertically option.
Here, the merge will occur towards the bottom of the selection, keeping the first row intact.
After vertical merge:
Note that we can also find these three options and Unmerge from the Toolbar menu (use drop-down).
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.
Like before, if your selection contains values in each cell, a warning pop-up will be presented before you can move further.
Step 3: Tap OK.
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.
In cell D2, we type in the formula:
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
This will help organize our data for 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.
CONCATENATE(string1, [string2, ...])
The function simply takes two or more string values and puts them together in a single cell.
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)
Issues with Merging Cells
On top of losing data, we still face a few more challenges when we merge cells.
- 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.
- 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.
- Copying merged cells also copies formatting. You cannot paste values of a merged cell separately, the formatting will also be carried over.
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.