How To Detect Which Cells Are Merged In Google Sheets

Question

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?

Answer 1

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

=MOD(COLUMN(),2)=0

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

---

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