Exceljet

Quick, clean, and to the point

Highlight values that don't exist

Excel formula: Highlight values that don't exist
Generic formula 
=COUNTIF(list,A1)=0
Explanation 

If you want to highlight cells with values that don't exist in a list or table elsewhere, you can apply conditional formatting with a simple formula that uses the COUNTIF function. For example, if you want to highlight any cells in the range A1:A10 that don't exist in the range C1:C10, select A1:A10 then create a conditional formatting rule that uses this formula:

=COUNTIF($C$1:$C$10,A1)=0

Note: with conditional formatting, it's important to enter the formula relative to the "active cell" in the selection, which is assumed to be A1 in this case.

How this formula works

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 10 cells in A1:D10, and A1 will change to the address of the cell being evaluated each time. Notice that C1:C10 is entered as an absolute address, so it won't change at all.

The key to this formula is the =0 at the end, which "flips" the logic of the formula. For each value in A1:A10,  COUNTIF returns the number of times the value appears in C1:C10. As long as the value appears at least once in C1:C10, COUNTIF will return a non-zero number and the formula will return FALSE.

But when value is not found in C1:C10, the COUNTIF returns zero and, since 0 = 0, the formula will return TRUE and the conditional formatting will be applied.

Named ranges for cleaner syntax

If you name the list you are searching (C1:C10 in this case) with a named range, the formula is simpler to read and understand:

=COUNTIF(list,A1)=0

This works because named ranges are automatically absolute.

Author 
Dave Bruns

Excel Formula Training

Learn key Excel formulas and functions with concise, clear videos. Master absolute/relative addresses, dates, text, named ranges, and tools for troubleshooting. Each video comes with a practice worksheet to follow along. Start today - instant access, 100% guarantee.

Click for more information.

OMG.....how easy! I will never do anything other than a Pivot Table if needed. -Annie
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course