This lesson describes how to compare two lists in Excel using conditional formatting. For example, let’s take two lists of NFL (National Football League) teams.
To highlight commands in the first list (not the second!), do the following:
- Select a range first A1: A18 and give it the name “firstList”.
- Then select a range B1: B20 and name it “secondList”.
- Now select a range A1: A18.
- On the Advanced tab Home (Home) select a team Conditional Formatting > Create Rule (Conditional Formatting > New rule).
- Выберите Use a formula to determine which cells to format (Use a formula to determine which cells to format).
- Enter the following formula:
=СЧЕТЕСЛИ(secondList;А1)=0=COUNTIF(secondList,А1)=0 - Set the formatting style and click OK.Result: The Miami Dolphins and Tennessee Titans are not on the second list.
Explanation:
- The formula =COUNTIF(secondList;A1) counts the number of commands in the second list that are identical to the command from the cell A1. If COUNTIF(secondList;A1)=0, then the commands from the cell A1 not in the second list. As a result, Excel colors the cell blue.
- Since we have chosen a range A1: A18 before applying conditional formatting, Excel will automatically copy the formulas to the rest of the cells.
- So the cell A2 contains the following formula:=COUNTIF(secondList,A2)=0,cell A3:
=COUNTIF(secondList;A3)=0 etc.
- To highlight commands in the second list that are not in the first, select the range B1: B20 and create a new rule using the formula below:
=СЧЕТЕСЛИ(firstList;B1)=0=COUNTIF(firstList,B1)=0 - Then set the formatting style (orange fill) and click OKResult: The Denver Broncos, Arizona Cardinals, Minnesota Vikings, and Pittsburgh Steelers are not on the first list.
Result: The Miami Dolphins and Tennessee Titans are not on the second list.
