To compare lists and highlight values that exist in one but not the other, you can apply conditional formatting with a formula based on the COUNTIF function. For example, to highlight values A1:A10 that don't exist C1:C10, select A1:A10 and create a conditional formatting rule based on this formula:
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
This formula is evaluated for each of the 10 cells in A1:D10. A1 will change to the address of the cell being evaluated, while 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 a 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 simple 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:
The EXACT function performs a case-sensitive evaluation and SUMPRODUCT tallies the result. As with the COUNTIF, this formula will return when the result is zero. Because the test is case-sensitive, "apple" will show as missing even if "Apple" or "APPLE" appears in the second list. See this page for a more detailed explanation.
To determine if a value exists in a range of cells, you can use a simple formula based on the COUNTIF function. In the example shown, the formula in D5 is: = COUNTIF ( rng , B5 ) > 0 where "rng" is the named range F4:F10. How this formula works...
If you want to find out what values in one list are missing from another list, you can use a simple formula based on the COUNTIF function. The COUNTIF function counts cells that meet supplied criteria, returning the number of occurrences found. If...
COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.