You are likely well versed in the standard layout of rows and columns in Google Sheets. Each row and column intersect at a cell and this is where you store all your data and formulas.

However, you might come across several reasons you would like to merge the cells in Google Sheets. The most common scenario is when you want to merge the column headings to span across multiple columns of the same category.

Using merged cells can help to organize your sheets, but it does have some drawbacks.

Imagine a situation where you have a company name heading placed across three columns and underneath each is a year of sales. Would you type the company name three times so it is above each year?

A more appropriate format might be to merge the three cells containing the company name heading into a single heading that spans across the three years!

Throughout this article, you will see four different ways of merging cells inside Google Sheets including how to automate this task with Apps Scripts.

Ready? Get your copy of the example workbook and let’s go! 😉

What Do Merge Cells Do?

In Google Sheets, you have a dedicated option to merge cells.

The option takes a range of cells from either the same row (horizontal) or column (vertical) and then combines them into a single large cell while keeping the text in the top left cell. It can also center the content inside the cell as well.

⚠ Warning: You will lose all data from the cells except the top left most cell if you merge them. The Good thing is that Google Sheets will show a message to warn you about this.

There are various ways of merging cells, and this post will discuss four of them.

How to Merge Cells from the Format Menu

The merging operation combines two or more cells and creates a single extended cell. The most common way to do this task is by accessing the option through the Format menu.

Suppose you have data as shown above, and you want to merge the cells B1:D1 together so that the years will have a standard heading above them as Sales.

To merge cells B1:D1 into a single cell, follow these steps.

1. Select the cells that you want to merge. In this case cell B1:D1.
2. Click on the Format menu from top ribbon.
3. Click on the Merge cells option fromt the menu.

You will have these four merging options available.

• Merge all – It merges all the cells from selection to combine those into a single extended cell with text from top left cell remaining. The option merges both vertically and horizontally.
• Merge vertically – It merges selected cells into an extended column and keeps the content from top cell only,
• Merge horizontally – It merges selected cells into an extended row and keeps the content from left cell only.
• Unmerge – It allows you to remove the merged formatting and convert the merged cell back into individual cells.

4. Since you want to merge the range B1:D1 into a single row, select the Merge horizontally option. Since you are only selecting a single row, the Merge vertically option will be grayed out for you.

Selecting Merge all will also work in the same way as Merge horizontally in this case, since you are trying to merge a single row.

That’s it! The cells are now merged horizontally. It looks better now. You have a common header for all years, and below that the three years are placed as secondary headers.

Pro Tip: On similar lines, you can merge the cells A1:A2 vertically. Just go through the Merge cells menu option through the Format menu and select the Merge vertically option this time.

After merging cells, you may want to adjust the row height or column width to properly format the cell.

How to Merge Cells from the Toolbar

Just in the case you are not aware, you can also merge cells through the Toolbar which is below the menus.

There you will find a dedicated Merge cells button with a dropdown for all the merging options.

1. Select the cells you want to merge. The selection this time will again be B1:D1. As soon as you select the cells, on Toolbar, the Merge cells option will be active.
2. You can hit the Merge cells button directly to get the desired result or you have the option to click on the dropdown menu placed beside and select the appropriate merging option.

Again, this tool has four different options that are the same as in the Format menu.

3. Choose the Merge horizontally option to merge the selected range B1:D1.

The merged output will be as shown in the screenshot above.

Pro Tip: Again, similar to the method discussed above, you can use the Merge vertically option to merge the cells A1:A2 across.

How to Merge Cells with a Keyboard Shortcut

It is sometimes time-consuming to use the Format menu or Toolbar command to merge the cells in Google Sheets.

You might prefer a keyboard shortcut instead! For such people, there are multiple shortcuts to merge cells.

• Alt + Shift, O, M, A is the keyboard shortcut to merge all selected cells.
• Alt + Shift, O, M, H is the keyboard shortcut to merge cells horizontally.
• Alt + Shift, O, M, V is the keyboard shortcut to merge cells vertically.
• Alt + Shift, O, M, U is the keyboard shortcut to unmerge the cells.

You can use these shortcuts to merge or unmerge cells inside Google Sheets.

Interestingly, you can also use the Spreadsheet compatible shortcuts to merge cells inside Google Sheets. You have to follow the steps below to do so.

1. From the ribbon, click on Help menu.
2. Select the Keyboard shortcuts option from the menu to open this in a new window.

1. In Keyboard shortcuts window that opens up, click on the Formatting menu from left pane.
2. You will see there are various spreadsheet compatible shortcuts associated with merging but those are grayed out for now.
3. To activate them, you need to toggle on the Enable compatible spreadsheet shortcuts option. This option is placed at the bottom of the window.

Once the spreadsheet compatible shortcuts are active, you will be able to use those as well to Merge all, Merge horizontally, and Unmerge the selected cells.

• Alt + H, M, A or Alt + O, M, H will merge cells horizonatally.
• Alt + H, M, M or Alt + O, M, A will merge all cells.
• Alt + H, M, U or Alt + O, M, U will unmerge cells.

Important: If these three options are not working for you while merging cells in Google Sheets, your sheet is probably locked for editing.

How to Unmerge Cells

Now, if you know how to merge cells in Google Sheets, you will definitely require ways to unmerge those cells.

There are three simple ways to do this task. You will learn those 3 methods in this section.

Merging cells is a type of formatting, so you could clear all the formatting from a range and this will unmerge any cells in your selected range.

However, you may only want to remove the merged cell and not any other formatting.

Unmerge Cells from the Format Menu

To unmerge cells through the Format menu, follow the steps below.

1. Select the cells which you want to unmerge, in this case cells B1:D1. Go to the Format menu from ribbon to open the formatting options.
2. Click on the Merge cells option.
3. Click on the Unmerge option to unmerge the cells.

The merged cell will be converted to individual cells and the top left most cell will contain any data from the merged cell as seen above.

Unmerge Cells from the Toolbar

To unmerge cells through Toolbar, follow the steps below.

1. Select the cells you want to unmerge, in this case cells A1:A2.
2. Inside Toolsbar, select the Merge cells option. It will be colored green to indicated your selected cells are are merged. Click directly on it to unmerge your selection.
3. Another option is to click on the dropdown besides the Merge cells command and select the Unmerge option fromt he list.

Both of these methods will work the exact same, so clicking on the Merge cells command directly will be the more efficient way!

Unmerge Cells with a Keyboard Shortcut

Unmerging cells can be achieved with the same keyboard shortcuts as used to merge cells.

• Alt + Shift, O, M, A will unmerge any merged cells.

You also have spreadsheet-compatible shortcuts to unmerge the cells.

• Alt + H, M, U
• Alt + O, M, U

Both of these keyboard shortcuts will unmerge your merged cells when the Enable compatible spreadsheet shortcuts option has been turned on.

Merge Cells Without Losing Data Using Apps Scripts

There is almost nothing in Google Sheets that can not be automated through Google Apps Scripts.

It is a JavaScript-based cloud development platform specifically built to automate tasks from Gmail, Sheets, Calendar, Drive, etc.

Check out the full guide to using Google Sheets Apps Scripts for more details about how to use this amazing tool.

You can use Apps Scripts to merge cells without losing the data in all the cells. You can concatenate the data from each cell and display it in the resulting merged cell.

The code can first join the data in each cell and separate it with a comma, it can then merge the cells and insert the joined text so you don’t lose any information when merging cells.

To open the Google Apps Script environment, go to the Extension menu from the ribbon and click on the Apps Script option. It will open the Apps Scripts environment in a new tab.

You can use the following Apps Script code to accomplish this task.

function ConcatMerge() {
  var selectedRange = SpreadsheetApp.getActive().getActiveRange();
  var selectedValues = selectedRange.getValues();
  var selectedColumns = selectedRange.getWidth();
  var selectedRows = selectedRange.getHeight();
  var allData = "";

  for (i = 0; i < selectedRows; i++) {
    for (j = 0; j < selectedColumns; j++) {
      if (i === 0 && j === 0) {
        allData = selectedValues[i][j];
      } else {
        allData = allData + ", " + selectedValues[i][j];
      };
    };
  };
  selectedRange.merge().setValue(allData);
};

This code will take the currently selected range and combine the contents as a comma-delimited string, then merge the cells and insert the concatenated string into the merged cell.

Explanation

1. You define a function that can hold the code and rename it as concatMerge().
2. On the second line, you use the SpreadsheetApp.getActive().getActiveRange() method to access the currently selected range of cells in the active workbook. The results are stored into a variable named selectedRange.
3. Then the values in this range are stored in the variable selectedValues.
4. The dimensions of the selected range are calucated and stored in the variables selectedColumns and selectedRows using the getWidth() and getHeight() methods. This is needed to loop throught the selectedValues and concatenate them.
5. You also define a new blank variable named allData that can hold the concatenated result temporarily.
6. Now, run two for loops for rows and columns each to populate the selectedValues into the allData variable.
7. While populating the values, add the strings together and separate them using comma as a delimiter. This is the part of concatenation. Your strings are not concatenated inside top-left cell of the selected range.
8. Then you call the merge() method on the range variable to merge the data from selected range. Finally, the setValue() method will set the value of the merged cell to the allData variable.

Hit the Run button from the Apps Script interface to run and execute this script. You will get the merged rows without losing your data.

Unmerge Cells and Restore Data with Apps Scripts

You have seen an Apps Script in the example above that can merge multiple cells without losing the data.

In this example, you will have a script that can unmerge the cells and restore the concatenated data into each unmerged cell.

To unmerge the cells and restore data in Google Sheets, use the script below.

function unMergeRestore() {
  var selectedRange = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();
  var selectedColumns = selectedRange.getWidth();
  var selectedRows = selectedRange.getHeight();
  var cellValue = selectedRange.getValues()[0];
  var splitCell = cellValue[0].split(", ");
  selectedRange.breakApart();
  var resultsArray = [];
  for (var i = 0; i < selectedRows; i++) {
    var rowData = [];
    for (var j = 0; j < selectedColumns; j++) {
      rowData.push(splitCell[i*selectedColumns + j]);
    }
    resultsArray.push(rowData);
  }
  selectedRange.setValues(resultsArray);
}

Explanation

1. You first define a function that will hold the code. You rename it as unMerge().
2. Then you get the active sheet by name. You use the getActiveSpreadsheet().getActiveRange() method on the SpreadsheetApp clause to get the currently selected merged cell. The result is stored into a varriable named selectedRange.
3. The range dimensions are stored in selectedColumns and selectedRows variables.
4. The value from the selected merged cell is stored in the variable cellValue.
5. This value is split based on the comma delimiter using the split() method and the resulting array of values is stored in splitCell.
6. You use the breakApart() method to unmerge the cells of the selected range.
7. After the cells are unmerged, you populate the range using with the splitCell array.

Hit the Run button to execute this script and you will now see that the cells are unmerged with values populated into the unmerged cells.

Conclusions

Throughout this article, you learned three different ways of merging the cells in Google Sheets. You can do this through the Format menu, the Toolbar, or using a keyboard shortcut.

Every time you merge cells, the data from the top-left cell is kept and the remaining data is discarded.

To overcome this issue, you can use an Apps Scripts solution! With Apps Scripts you can concatenate data from the different cells together and after merging them, you can store the concatenated data in the resulting merged cell.

You can also build an Apps Script that can unmerge the cells, and split the concatenated strings to populate the separated text into different cells.

Did you know all these methods for merging cells? Do you know any other merge tricks? Let me know in the comments below!