How to compare two lists and highlight differences

In this video, we'll look at how to compare two lists using conditional formatting. This is a great way to visually highlight missing items in a list.

Here we have two lists. Both lists contain the same number of items, but each list is slightly different.

We can use conditional formatting with a formula to quickly find and highlight the differences.

First, I'm going to name each list. The first list I'll call list1, and the second list I'll call list2. It's not necessary to name the lists, but it will make our formulas easier to read and understand.

For the formula, we can use the COUNTIF function. COUNTIF takes two arguments, a range, and a criteria. We can use COUNTIF to count the number of times that each item in list1 appears in list2.

If COUNTIF returns zero, we know the item is not in list2.

This will work perfectly for conditional formatting since we can easily flag items where the count is zero with a formula.

For the first rule, I'll select list1 and create a new conditional formatting rule, that uses a formula.

We want to use the same formula we just tested, with a little twist. We want to return TRUE if the count is zero. This will trigger the rule.

Now you see that Coconut and Fig are not in the second list.

For the second rule, we want to count items in list1. As before, if we get a count of zero, the rule should return TRUE.

Conditional formatting tells us instantly that Guava and Tangerine are not in list1.

This example is simple and each list is short, but you can use the same approach with lists of hundreds or thousands of items.