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:
The conditional formatting formula in 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