How To Merge Cells In Google Sheets And When To Be Careful

In this tutorial, you’ll learn how to merge cells in Google Sheets, when to use merged cells in Google Sheets, the pros and cons of using merged cells, and finally, how to identify them with Apps Script.

How To Merge Cells In Google Sheets

There are two ways to merge cells in Google Sheets, through :

1. the Format menu, or
2. the quick access button on the toolbar

Here’s how you access items (1) and (2) through the menu and toolbar:

(Ok, there is a 3rd way. We’ll look at merged cells with Apps Script at the bottom of this article.)

There are three different types of merge action you can perform on cells:

Horizontal Merge

Merge across rows of cells using the horizontal merge option “Merge horizontally“:

Vertical Merge

Merge down columns of cells using the vertical merge option “Merge vertically“:

How To Combine Cells In Google Sheets With A Full Merge

If you want to merge all the cells in your range into one single cell, use the “Merge all” option:

How To Unmerge Cells In Google Sheets

This one is super easy!

Highlight the merged cells, and choose Format > Merge cells > Unmerge from the menu or Unmerge in the quick access toolbar.

What happens to cell content when cells get merged?

The value in the top-left cell of the merge range takes precedent and overwrites any other data in the range you’re merging.

You’ll see the following warning popup if you try to merge non-empty cells:

If you click OK, the data in the top-left cell remains but any other data is deleted.

In this example, the data in cell B1 disappears when we merge the cells.

There is no value in cell B1 anymore. The formula placed in E1, pointing to B1, now returns a null value (empty).

Should You Use Merged Cells?

Merged cells divide opinion in the spreadsheet world, perhaps more than any other feature.

They’re easy to apply, but they must be used with caution.

Yes, they’re useful in specific situations as you’ll seen in this article, but they will cause trouble if used incorrectly.

The golden rules with merged cells are:

• Use them for formatting only
• Never use merged cells to store data in your datasets

Avoiding merged cells in datasets is one of the best practices for working with data in Google Sheets.

They cause issues because they break the cardinal rule of each piece of data existing in its own cell. Which range does the merged cell belong to? They can cause issues with formulas and pivot tables, or when you sort, copy/paste, or move data.

Consider this example, where a dataset includes a merged cell:

Notice how the formula in columns B looks as if it sums 1 + 2 + 3 + 4 (which is 10) but gives the answer 6.

That’s because of the merged cell on row 4. The value 4 is actually included in column A and not in column B. B4 is null (i.e. an empty cell):

If you takeaway nothing else from this article, please never use merged cells in your data analysis.

With that caveat out the way, when do we use them?

When To Use Merge Cells In Google Sheets

Use merged cells to create text cells that span multiple columns or multiple rows.

The most common use case is to showcase a title that applies across the top of your document:

Similarly, you might merge cells along the top and sides of a table to add context. See row 1 and column A in this example:

Merged cells are a useful tool to have in your tool-belt if you create customized front-ends for your Sheets, so they don’t look like plain spreadsheets.

For example, consider this custom header for a Facebook Ads dashboard, where the range L2:S4 has been merged:

(See what the final dashboard looks like here: Dashboard Design Checklist: From a Blank Google Sheet to Business Insights)

Finding Merged Cells In Your Google Sheet using Apps Script

(If you’re new to Apps Script, check out my tutorial Google Apps Script: A Beginner’s Guide)

Merged cells can be hard to see sometimes, especially in larger or more complex Sheets.

You can use Apps Script to build a tool to highlight them for you.

Here’s a simple implementation:

Using the getMergedRanges() method will find all instances of merged cells in your Sheets.

Here’s a simple script to identify them:

// highlight merged cells
function highlightMergedCells() {
  
  // get sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1');

  // get the merged ranges
  const range = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());
  const mergedRanges = range.getMergedRanges();

  // string variable to hold merged cell info
  let mergedCellString = '';
  
  // loop over merged ranges to highlight and return details
  mergedRanges.forEach((rng,i) => {

    // highlight merged cells with yellow background
    rng.setBackground('yellow');

    // gather the merged cells info and add to string
    mergedCellString = mergedCellString + 'Merged Cell Group ' + sheet.getName() + ' (' + (i+1) + '):\n'; // which merged cell group
    mergedCellString = mergedCellString + 'Range: ' + sheet.getName() + '!' + rng.getA1Notation() + '\n'; // what is the merged cells range
    mergedCellString = mergedCellString + 'Contents: ' + rng.getDisplayValue() + '\n\n'; // include the merged cells contents
  
  });

  // output summary pane of merged cells info
  SpreadsheetApp.getUi().alert(mergedCellString);

}

This tool can be extended easily to cover all the sheets within a single Google Sheet file and add an option to unhighlight the merged cells.

See the merged cells repo on GitHub.

Merging Cells With Apps Script

You can use Apps Script to not only find merged cells but also to actually merge cells in your Sheets.

Use the merge(), mergeAcross() and mergeVertically() methods to programmatically merge cells in Google Sheets.