How to highlight non present values using conditional formatting

---

---

Say you want to see how many people have attended an event that you have organized. Attendees sign in as they enter the event and you are recording their attendance using MS excel. Here is the easiest and fastest way to highlight those who were absent …. Believe me this is much easier than using a VLOOKUP or other methods.

​In the example below, the “Confirmed Invitees” Column contains a list of all people who are expected to attend the event and the “Attendance List” column contains the list of those who have actually attended in the sequence of their arrival. 

​In order to highlight the list of absent individuals just follow these six very simple steps:

Step 1] Select the range in which you are looking for missing data. In this case, the values in the ”Confirmed Invitees” column. 

Step 2] in the Home tab of the ribbon go to “Conditional Formatting” and select “New Rule”

Step 3] Click on “use a formula to determine which cells to format”

Step 4] Enter a formula similar to the one below:

How this formula works?
For each value in the A2:A14 range, COUNTIF will return the number of times the value appears in the C$2:C$11 range. In the formula above the C$2:C$11 range is an absolute range because we have used the $ symbol so the reference won’t change, however; A2 will change to the address of the subsequent rows (i.e. A3:A14) that are being evaluated each time.
For any value in cells A2:A14 which does NOT appear at least once in the C$2:C$11 range , COUNTIF will return a zero number and the formula will return TRUE and every time a True is returned the Conditional Formatting criteria will be applied.

Step 5] Click on “Format” and in the “Fill” page select a color of your choice (It should be different from the existing background color). Then click “OK”

Step 6] Click “OK” a second time 

Here is the desired outcome.

---