Conditionally formatting cells if their value equals any value of another column

No formulas required. This works on as many columns as you need, but will only compare columns in the same worksheet:

NOTE: remove any duplicates from the individual columns first!

1. Select the columns to compare
2. click Conditional Formatting
3. click Highlight Cells Rules
4. click Duplicate Values (the defaults should be OK)

5. Duplicates are now highlighted in red

   • Bonus tip, you can filter each row by colour to either leave the unique values in the column, or leave just the duplicates.

Here is the formula

create a new rule in conditional formating based on a formula. Use the following formula and apply it to $A:$A

=NOT(ISERROR(MATCH(A1,$B$1:$B$1000,0)))

here is the example sheet to download if you encounter problems

---

UPDATE
here is @pnuts's suggestion which works perfect as well:

=MATCH(A1,B:B,0)>0