Question: How do I highlight duplicates in two lists using conditional formatting? In other words, how do I highlight cell values in List 1 that also exist in List 2? And vice versa?

highlight-common-values-in-two-lists-using-conditional-formatting-in-excel

Answer:

highlight-common-values-in-two-lists-using-conditional-formatting-in-excel3

The conditional formatting formula in A2:A6:
=COUNTIF(List2, $A2)>0

The conditional formatting formula in B2:B6:
=COUNTIF(List1, $B2)>0

Named ranges

List1 (A2:A6)
List2 (B2:B6)
What is named ranges?

The downside with this solution is that it does not recognize duplicates existing only in one list. As you can see in List2, there are two duplicates DD but they are not highlighted. To solve this problem see below.

How to also highlight duplicate values existing in only one list

highlight-common-values-in-two-lists-using-conditional-formatting-in-excel2

The conditional formatting formula in A2:A6:
=OR(COUNTIF(List2, $A2)>0, COUNTIF(List1, $A2)>1)

The conditional formatting formula in B2:B6:
=OR(COUNTIF(List1, $B2)>0, COUNTIF(List2, $B2)>1)

How to customize the formula to your excel workbook

Change the named ranges and change the cell reference  $A2 and  $B2 in formulas  to the starting points of your lists.

Download excel example file
highlight-common-values-in-two-lists-using-conditional-formatting-in-excel.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this blog post:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

OR(logical1, logical2, ...)
Checks whether any argument are TRUE and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.

This blog article is one out of five articles on the same subject.

Related posts:

Highlight the second or more duplicates in two lists using conditional formatting in excel

Lookup between two lists of data to highlight missing data using conditional formatting in excel

Highlight duplicates using conditional formatting in excel

Highlight duplicate values in a range using conditional formatting in excel

Highlight unique values and unique distinct values in a range using conditional formatting in excel