Google Sheets Cheat Sheet

How To Merge Cells In Google Sheets

Select the cells you want to merge wrap and in the toolbar click on one either the merge icon or one of the options in the dropdown:

shows the merge cells icon and its dropdown list in the google sheets toolbar

Click on what you'd like to learn:

Be careful when merging cells as doing so can delete data and prevent you from using other spreadsheet features.

How To Merge Cells In Google Sheets

Not on desktop? Click for mobile instructions: iPhone/iPad and Android.

Merge Cells Using The Toolbar

STEP 1: Select the cells you want to merge by clicking and dragging across cells. Selected cells are highlighted and bordered in blue.

demonstration of how to select cells by clicking and dragging across a sheet on the google sheets desktop app

You can only merge a single range of cells at once. Making multiple selections will mean the merge cells option is unavailable to you.

STEP 2: Click on the 'Merge cells' icon in the toolbar:

close up image of what the merge cells icon looks like in the google sheets toolbar

That's it! Your selection is now merged into a single cell.

If you want more merging options, instead of clicking 'Merge cells' icon in the toolbar, you can instead click on the 'Select the merge type…' dropdown arrow:

close up image of what the merge cells dropdown looks like in the google sheets toolbar

You will be presented with the following options (with some options unavailable depending on your selection):

TRY IT: You can practice using the 'Select the merge type…' menu right now:

ABCDE
1
2
3
4
5
6
7
8

Merge and center in Google Sheets is a two-step process:

  1. Merge the cells
  2. Center align them

Merge Cells Using The Format Menu

STEP 1: Select the cells you want to merge. Selected cells are highlighted and bordered in blue:

demonstration of how to select cells by clicking and dragging across a sheet on the google sheets desktop app

You can only merge a single range of cells at once. Making multiple selections will mean the merge cells option is unavailable to you.

STEP 2: Click on the Format menu item, hover over Merge cells in the dropdown and in the menu that appears, click on an available option:

highlighting how to find the merge cells option in the format menu

That's it! Your selection is now merged.

Merge Cells Using Keyboard Shortcuts

STEP 1: Select the cells you want to merge. Selected cells are highlighted and bordered in blue:

demonstration of how to select cells by clicking and dragging across a sheet on the google sheets desktop app

You can only merge a single range of cells at once. Making multiple selections will mean the merge cells option is unavailable to you.

STEP 2: Use any of the following Google Sheets shortcuts for merging cells:

ABC
1DescriptionWindowsMac
2Merge all cellsAlt+H,M,M or Alt+O,M,ANone
3Merge cells horizontallyAlt+H,M,A or Alt+O,M,HNone
3Merge cells verticallyAlt+O,M,VNone
4Unmerge cellsAlt+H,M,U or Alt+O,M,UNone

Here's what the Windows shortcuts look like:

demonstration of how to use keyboard shortcuts to merge cells in the google sheets desktop app

You can search all documented Google Sheets shortcuts for both Windows and Mac here.

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

How To Merge Cells In Google Sheets Without Losing Data

If more than one of the cells you're trying to merge together contains data, you will be presented with the following warning:

google sheets error message that warns users that only the top-leftmost value in the selected range will be kept if the user proceeds with merging the cells

Google Sheets is reminding you that only the top-left most data is kept when you merge cells that contain data. The data from the other cells will be deleted if you select 'OK'.

This isn't always ideal.

If you need to keep all data from the selected cells you can simply join the data before you merge.

There's a function for that!

=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])

Using this function will allow us to combine the data in our range into a single cell which we can then have as a merged cell.

The delimiter is just the piece of text that gets inserted between each data point.

Let's work through an example. In the sheet below we want to merge cells A1:A6 into a single cell:

AB
1We
2want
3to
4keep
5this
6data

In cell B1 we can enter this formula:

=TEXTJOIN(" ", TRUE, A1:A6)
AB
1WeWe want to keep this data
2want
3to
4keep
5this
6data

You can copy and paste the data over (using Ctrl+C or ⌘+C then Ctrl+V or ⌘+V) and then merge cells A1:A6:

AB
1We want to keep this data
2
3
4
5
6

You can use any delimiter you like instead of " ". A personal favorite of mine is CHAR(10) which enters a line break between each data point:

=TEXTJOIN(CHAR(10), TRUE, A1:A6)
AB
1WeWe
want
to
keep
this
data
2want
3to
4keep
5this
6data

How To Merge Cells In Google Sheets iPhone And iPad App

STEP 1: Select the cell, range, column, or row you want to wrap by touching them.

STEP 2: Touch the formatting icon above the sheet:

location of the formatting icon in the google sheets iphone and ipad app

STEP 3: Navigate to the 'Cell' tab of the formatting menu and check the 'Merge cells' option:

location of the merge cells option in the cell formatting menu in the google sheets iphone and ipad app

Here's a clip of the iPhone/iPad cell merging process:

demonstration of how to merge cells in the google sheets iphone and ipad app using the cell formatting menu

How To Merge Cells In Google Sheets Android App

STEP 1: Select the cell, range, column, or row you want to wrap by touching them.

STEP 2: Touch the formatting icon above the sheet:

location of the formatting icon in the google sheets android app

STEP 3: Navigate to the 'Cell' tab of the formatting menu and check the 'Merge cells' option:

location of the merge cells option in the formatting menu of the google sheets android app

Here's a clip of the Android cell merging process:

demonstration of how to merge cells using the cell formatting menu in the google sheets android app

What Are Merged Cells?

Merged cells are rectangular groups of adjacent cells that have a single border and can contain only one piece of data (like a single cell).

When cells are merged, the internal borders between the individual cells disappear.

Because of the potential errors they cause and the fact that they reduce the number of cells available for calculation and manipulation, merging cells, in my opinion, restricts the functionality of spreadsheets.

However! That doesn't mean they don't have a place:

Why Merge Cells In Google Sheets?

Merged cells make spreadsheets more readable and useful by more effectively aligning things.

This could be headings and their corresponding data:

ABC
1Q1
2JanFebMar
3$4,064$4,025$3,954
4$2,260$4,565$1,782
5$4,679$3,743$1,948

Vs

ABC
1Q1
2JanFebMar
3$4,064$4,025$3,954
4$2,260$4,565$1,782
5$4,679$3,743$1,948

This might seem like an insignificant difference (especially when Q1 could just as easily be placed in cell B1), but it can be important for customer-facing reports that need to look professional while also being easily understood.

Merging cells is useful in a more complex setting when formatting and aesthetics is important.

If you have a sheet that generates a customer invoice as a PDF, formatting throughout that sheet is key to making the end document fit onto a single page.

Similarly if you have a dashboard, merging cells can help you get the most relevant information into the right locations to make it more visible and easily accessible.

The key is to use merged cells only when absolutely necessary to avoid the possibility for them to create errors and other functionality headaches down the road.

For me, this means trying to use them only in purely aesthetic sheets that are compiling and presenting data.

In sheets that store and/or analyze data, I like to avoid merging cells.

How To Find Merged Cells In Google Sheets

Finding merged cells in a sheet can be difficult (especially large ones).

Here's a couple of methods that can speed up the process:

Finding Merged Cells Using Borders

STEP 1: Select every cell using the shortcut Ctrl+A or ⌘+A. Alternatively, you can click on the square to the top left of the sheet (above the '1' row label and to the left of the 'A' column label).

STEP 2: Choose the 'All borders' option from the 'Borders' dropdown in the toolbar:

demonstration of how to find merged cells in google sheets by selecting all cells and giving them borders

There are a couple of downsides to this method:

  1. You can still miss some merged cells (especially horizontally merged cells) as the borders alone don't make them stand out that much.
  2. You have to do it for each sheet in a spreadsheet which can make it repetitive.

Finding Merged Cells With A Script

This method uses Google Apps Script to change every merged cell in a single sheet or every sheet of a Google Sheet to have a black background and red border:

demonstration of how to find merged cells in google sheets by using the provided google apps script code

It takes a couple of seconds to do the whole spreadsheet and you can't miss any of them.

Here's the code:

Dark theme
Copy code
//Custom Menu
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Find Merged Cells')
    .addItem('🔍 Find in this sheet', 'findInThisSheet')
    .addItem('🔎 Find in all sheets', 'findInAllSheets')
    .addToUi();
}

//Find merged cells in the current sheet
function findInThisSheet() {

  //Get the spreadsheet and individual sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();

  findMerged(sheet);

}

//Find merged cells in the whole spreadsheet
function findInAllSheets() {

  //Get the spreadsheet and individual sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

  //Loop over sheets
  sheets.forEach(sheet => {

    findMerged(sheet);

  });

}

//Find merged ranges and change their formatting
function findMerged(sheet) {

  //Get all merged ranges in the sheet
  let allCellsInSheet = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  let mergedRanges = allCellsInSheet.getMergedRanges();

  //Loop over the merged ranges changing the background and border
  mergedRanges.forEach(mergedRange => {

    mergedRange
      .setBackground('black')
      .setBorder(true, true, true, true, null, null, 'red', SpreadsheetApp.BorderStyle.SOLID_THICK);

  });

}

I've included a custom menu to make it easy to execute the script from the sheet instead of the Google Apps Script IDE.

Simply save the script and refresh the sheet to see the Find Merged Cells menu appear to the right of Help in the main menu.

Here is a list of errors you can encounter when using merged cells. They range from mildly inconvenient to a huge pain.


Merging cells in a table of data can cause analysis issues.

Numbers right-align by default. Data in merged cells is stored in the top-left most cells with the other merged cells being, in a sense, empty.

This can lead to issues like this:

ABC
1Q1Q2
2Dwight$300$300
3Jim$200$200
4Andy$100
5Total$600$500

The SUM functions in B5 and C5 are correct, it just looks like they're wrong.

A quick glance at the table would make you think Andy doesn't have a Q1 entry, only an entry for Q2. However, the opposite is true.

A merged cell is to blame.

The data is stored in B4 but it's presented in C4.

This is one of the reasons why merged cells and data analysis don't mix well.


The data in the top-left-most cell of a merged range takes precedence and overwrites any other data in the range you're merging.

This data is erased and can't be recovered later (without a long series of undos (Ctrl+Z or ⌘+Z)).

merged cells preserving the top-leftmost data and deleting the rest in google sheets

Unmerging cells places the data the merged range contained in the top-left of the unmerged cells regardless of where it was before merging.

This might be quite far from where it was and where you intend it to be.

process of data moving when merging and unmerging cells

You can't move a column or row across merged cells.

When you've got big tables rearranging columns can be necessary and having to unmerge and merge headings just to accomplish this can be time-consuming.

error message shown when trying to move a column across a merged range

Formatting you want to apply to a column or row won't be applied unless the top-left most cell of a merged range is included in the selection.

demonstration of how formatting is only applied to a merged range if the top-leftmost cell is included in the selection

Ranges that contain a merged cell can't be sorted unless the 'merges must be entirely within the sort range'.

error message saying that sorting a range only works if merged cells are fully encompassed within the sort range

When selecting by clicking and dragging across a sheet, merged cells cause you to select additional to encompass the entire merged area which leads to unnecessary cells being selected.

demonstration of how click and drag selection over merged cells widens or lengthens the selection to encompass more data than intended

The same thing happens when creating formulas:

selecting cells across merged ranges in a formula also creates issues of encompassing unwanted data by widening or lengthening the intended selection

When you copy a merged cell, you're copying all of the merged cells - not just the single merged cell.

This causes issues when you subsequently paste what you've copied.

If you paste normally it will place a similarly sized merged cell wherever you're pasting, overwriting nearby data in the process.

If you paste as values it will still overwrite nearby data with blank cells.

You can accidentally lose important data because of this.

demonstration of how pasting a merged range normally or as values has the potential to overwrite nearby data

I don't think this error will come up very often, but here it is anyway:

When you have a filter on a set of data, you can't merge cells that intersect an existing filter (that go across the boundary of the filter).

error message presented to users who try to merge cells that intersect an existing filter

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.