Here is how you can determine what items are not in a list using Conditional Formatting:
You can download the file here to follow along. If you see a preview, look for the download arrow in the upper right hand corner.
In column “D” I have a list of names. I want to check which names in columns “F” & “G” are on my main list. In column “F”, I want to highlight those that are on my list, in column “G” I want to highlight those that are not on my list.
We will use Conditional Formatting for this process. First, we’ll tackle column “F” to determine which of those are in my list in column “D”. I highlight the names in column “F” and select Conditional Formatting from the Styles group on the Home tab and select New Rule:
Then I’ll select “Use a formula to determine which cells to format”, and the following box appears for me to enter my formula:
Here I will enter the following formula: =COUNTIF($D$2:$D$21,F2)>0. The syntax for COUNTIF is =COUNTIF(range,criteria). The range is the List in column “D”. Note that I’ve made this range an absolute cell reference using the “$”. However, I’ve left the criteria, which is the name in column “F” as relative, so that as the process goes through each row, the “List” stays constant, but the criteria for each name in rows 2 through 10 are evaluated:
The logic is that Excel will count the number of times each name in column “F” is on the list in column “D”. If that number is greater than zero, it will format it accordingly.
I then click on Format, then the Fill tab and select the color I want:
Click OK and OK again, and the names that are on my list are highlighted:
The only difference in the formatting in column “G” is that we will want to highlight the cells when the count = 0. So our formula will be:
It’s that simple! Notice how one list is just the inverse of the other.