Lesson 230: Compare two lists in Excel

This article will guide you to compare two lists in Excel.

Compare two lists in

Here I have 2 lists to compare

To highlight the names in the first list but not in the second list, we do the following:

1. Select range A1:A18 and name it firstList, choose range B1:B20 and name it secondList

2. Select range A1:A18

3. On the Data Tab, select Conditional Formatting

4. Select new rule

5. Select Use a formula to determine which cells to format

6. Enter the formula =COUNTIF(secondList,A1)=0

7. Select formatting style and click OK

Result. The Miami Dolphins and Tennessee Titans are not on the list.

Explanation: =COUNTIF(secondList,A1 counts the number of names in the second List as equal to the names in cell A1. If COUNTIF(secondList,A1) = 0, the group in cell A1 is not in the second list. As a result, Excel will fill the cell with blue color.

8. To highlight groups in the second list that are not in the first list.

Select the range B1: B20, create a new rule with =COUNTIF(firstList,B1)= 0 and change the color of the bar to orange

In addition, you can also view many other excel articles here.