Author: Oscar Cronquist Article last updated on December 22, 2017

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? And vice versa? To do this I use conditional formatting.

To extract a list of missing values using excel formula, check this blog post What values are missing in List 1 that exists i List 2?

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?

How to customize the formula to your excel workbook
Change the named ranges and change the cell reference  $A2 in formula =COUNTIF(List2, $A2)=0 to the starting point of your list

Download excel example file
(Excel 97-2003 Workbook *.xls)

Functions used in this blog post:

Counts the number of cells within a range that meet the given condition