How to Find Merged Cells in Google Sheets (3 Simple Tricks)

When using Google Sheets, you might merge cells to make headers or sections look cleaner. But if you’re working with a large table, those merged cells can cause problems. For example, sorting or filtering might not work as expected. The hard part is that merged cells don’t always stand out right away, so they can be easy to miss. In this guide, you’ll learn a few ways to quickly find and manage merged cells in your spreadsheet.

In this article, I’ll show you a few easy ways to find merged cells in your sheet. You’ll learn how to check for them manually, use a simple trick to spot them, and even run a quick script that lists them all for you.

function findMergedCells() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange(); // Get the full data range
  const merged = range.getMergedRanges(); // Get merged cells from the range
  let result = "Merged Ranges:\n";
  if (merged.length === 0) {
    result += "None found.";
  } else {
    merged.forEach(r => {
      result += r.getA1Notation() + "\n";
    });
  }
  SpreadsheetApp.getUi().alert(result);
}

Spreadsheets in Google Sheets can have merged cells that are hard to notice. These merged cells might create problems when you try to sort, filter, or edit the data. If you are working with a large sheet, it can take a long time to find them all manually.

Google Apps Script can help with this. It is a tool inside Google Sheets that lets you write small programs. You can quickly find and list all the merged cells in your spreadsheet using a short script. This makes it easier to fix layout problems and keep your data organized.

We’ll use a sample Sales Report sheet where the Region column has merged cells across rows, for example, “North” is merged across two rows for two salespeople. This setup helps you group data visually, but can interfere with filtering or sorting, making it important to identify merged cells quickly.

Steps:

➤ Open your Google Sheet.
➤ Go to the top menu and click Extensions >> Apps Script.

➤ Delete any code already present in the script editor.
➤ Paste the following code into the editor:

function findMergedCells() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange(); // Get the full data range
  const merged = range.getMergedRanges(); // Correct: get merged cells from the range
  let result = "Merged Ranges:\n";
  if (merged.length === 0) {
    result += "None found.";
  } else {
    merged.forEach(r => {
      result += r.getA1Notation() + "\n";
    });
  }
  SpreadsheetApp.getUi().alert(result);
}

➤ Click the disk icon to save the project, then name it something like “Merged Cell Finder”.

➤ From the dropdown menu next to the function name (findMergedCells), click Run. The first time you run it, Google will ask you to authorize permissions.

➤ A pop-up will appear showing all the merged cell ranges in your current sheet, like:

This script helps you instantly identify merged areas without scrolling through every cell manually, saving time and avoiding hidden formatting issues.

If you’re not sure whether a sheet contains any merged cells, the easiest way to check is by using the Format menu. Google Sheets doesn’t highlight merged cells automatically, but it does let you know when merged ranges are present by enabling the Unmerge option. This method is great when you’re scanning an entire sheet or just want a quick manual check.

Steps:

➤ Open your Google Sheet.
➤ Select the entire worksheet by pressing Ctrl + A (or Cmd + A on Mac). Alternatively, click the blank square at the top-left corner between the row numbers and column letters.
➤ In the top menu, click Format, then hover over Merge cells.
➤ If you see the “Unmerge” option is active (clickable), this means there are merged cells somewhere in the selection.

➤ Click Unmerge to break up all merged cells in the sheet, or leave it for now and scroll through the sheet to inspect where the merges are manually.

This method doesn’t highlight the exact cells but gives a fast way to confirm whether merged cells exist in the sheet. It’s especially useful when troubleshooting sorting or filter issues.

Google Sheets does not allow filters to be applied when merged cells are present within the filter range. You can use this limitation to your advantage. If you’re unsure where merged cells are causing issues, trying to apply a filter will trigger an error that helps identify their location.

This method is especially helpful when working with imported tables or large datasets where merged cells may be hidden in the middle of the sheet.

Steps:

➤ Click on any cell inside your dataset, for example, one of the column headers.
➤ Go to the top menu and select Data >> Create a filter.

➤ If your sheet has merged cells in the range you’re trying to filter, Google Sheets will display an error message:  “You can’t create a filter over a header containing vertical merges”

➤ Read the error message carefully. It often highlights which range or row is causing the problem.
➤ Scroll through that section of the sheet and look for cells that are larger than usual or contain text that spans multiple columns or rows. These are the merged cells preventing filtering.

This trick is especially useful for troubleshooting filter problems and finding merged cells that are not obvious at first glance.

Frequently Asked Questions

How do I find merged cells in Google Sheets quickly?

You can detect merged cells by selecting your entire sheet and going to Format >> Merge cells. If the “Unmerge” option is clickable, that confirms merged cells are present somewhere in the sheet.

Why does Google Sheets prevent sorting when merged cells are present?

Google Sheets requires a uniform cell structure for sorting and filtering. Merged cells break that consistency, which is why it blocks sorting or filtering when such cells are found in the selected range.

Can I highlight merged cells automatically using conditional formatting?

Conditional formatting cannot directly detect merged cells. However, you can fill your sheet with a light color, then unmerge everything. The cells that visually change were previously merged, helping you identify them manually.

Is there a way to programmatically list all merged cells in a sheet?

Yes. You can use a Google Apps Script that scans your sheet’s data range and returns a list of all merged cell addresses using getMergedRanges() and getA1Notation(), which is perfect for large or hidden datasets.

Are there any add-ons or tools to help manage merged cells?

Yes, tools like Power Tools or Ablebits in the Google Workspace Marketplace offer utilities to highlight, list, or unmerge merged cells. These are helpful for cleaning up imported or shared spreadsheets.

Wrapping Up

Merged cells can sometimes sneak into your spreadsheet and cause trouble without you noticing. But with the right methods, it’s easy to find and fix them. For smaller sheets, a quick manual check is usually enough. Using a simple script can save a lot of time if you’re working with a big spreadsheet. No matter the size of your data, these tips will help you keep everything neat and working smoothly.