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 applied to B3:B7:
The conditional formatting formula applied to D3:D7:
Explaining CF formula applied to B3:B7
The COUNTIF function counts the number of time the value in cell B3 exists in cell range D3:D7.
Now if the COUNTIF function returns 0 (zero) that means that the value is not found in the other cell range and the logical expression returns TRUE.
and returns TRUE.
Cell B3 is highlighted, the value is not found in the other list.
Download excel example file
(Excel 97-2003 Workbook *.xls)