How To Compare Two Lists in Excel
These example describes how to compare two lists using conditional formatting.
Example 1: Compare Lists of Customers for May 2010 and April 2010.
- Select cells in both lists (select first list, then hold CTRL key and then select the second)
- Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values
- Press ok.
See other examples
Example 2: You may have two lists of NFL teams.
To highlight the teams in the first list that are not in the second list, execute the following steps.
1. First, select the range A1:A18 and name it firstList, select the range B1:B20 and name it secondList.
2. Next, select the range A1:A18.
3. On the Home tab, in the Styles group, click Conditional Formatting.
4. Click New Rule.
5. Select ‘Use a formula to determine which cells to format’.
6. Enter the formula =COUNTIF(secondList,A1)=0
7. Select a formatting style and click OK.
Result. Miami Dolphins and Tennessee Titans are not in the second list.
Explanation: =COUNTIF(secondList,A1) counts the number of teams in secondList that are equal to the team in cell A1. If COUNTIF(secondList,A1) = 0, the team in cell A1 is not in the second list. As a result, Excel fills the cell with a blue background color. Because we selected the range A1:A18 before we clicked on Conditional Formatting, Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =COUNTIF(secondList,A2)=0, cell A3 =COUNTIF(secondList,A3)=0, etc.
8. To highlight the teams in the second list that are not in the first list, select the range B1:B20, create a new rule using the formula =COUNTIF(firstList,B1)=0, and set the format to orange fill.
Result. Denver Broncos, Arizona Cardinals, Minnesota Vikings and Pittsburgh Steelers are not in the first list.