Search code examples
google-sheetsmergeformattingformulags-conditional-formatting

How To Detect Which Cells Are Merged In Google Sheets

While trying to remove duplicate phone numbers from a relatively large list I recently combined, Google Sheets presented me with this error message:

There was a problem. Your selection contains a merged cell. To remove duplicates, all cells must contain the same number of rows and columns.

The formula I used to test and try to expose merged cells within Google sheets was:

=if(columns(A2:F2) = 6, "True", "False")

But this always returned true Because even though the cells may be merged they are still considered individual cells.

I am wondering if there is an easy way to discover and sort out these cells in Google Sheets. Excel used to have a very simple way of doing it but has since removed the functionality.

Any ideas?

Solution

  • if you have such an option you can use Conditional Formatting to check for merged cells like:

    =MOD(COLUMN(),2)=0

    enter image description here

    where you can immediately spot merged cells where the color pattern is interrupted

    in the same manner you can use this formula for rows:

    =MOD(ROW(),2)=0

    enter image description here

    or you can play with scripts to find merged cells: https://issuetracker.google.com/issues/36753230