Google Sheets – Join Multiple Ranges with Conditional Formatting

conditional formattingformulasgoogle sheets

I have a simple conditional formatting custom formula which highlights duplicate values in a column:

=countif(A:A,A1)>1

I have another column on another sheet (same workbook) which I would like to include in the search for duplicates. I can combine the ranges separately using the following formula:

=query({sheet1!A:A; sheet2!A:A},"where Col1 <>''")

but I can't get that formula to work in conditional formatting. What I have (which doesn't work), is:

=countif(query({sheet1!A:A; sheet2!A:A},"where Col1 <>''"),A1)>1

I don't understand how "where Col1 <>''" works or why I need it, but even so, the formula doesn't work.

The question is, how can I (using conditional formatting) highlight duplicate cells in column A, searching across multiple ranges for duplicates?

Best Answer

  • when referencing another sheet in conditional formatting, you need to use INDIRECT

  • conditional format rules are per sheet meaning that you need 1 separate rule per every sheet

  • "where Col1 <>''" means to select only those rows from a query which are not empty in the 1st column

  • to show duplicates between 2 ranges use this custom formula:

    =COUNTIF({A:A,INDIRECT("Sheet2!A:A")},A1)>1

    0

Related Topic