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


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?

1 Answer 1

  • 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:



  • 1
    Oh that's just superb - thank you!
    – essdeeay
    Commented Mar 16, 2019 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.