How to Compare 2 Lists in Excel: Find Differences Quickly

Comparing two lists in Excel to identify differences or matches can be a time-consuming task if done manually. Fortunately, Excel offers powerful features like conditional formatting and the COUNTIF function to streamline this process. This guide will show you how to effectively compare two lists in Excel and highlight the items that are unique to each list.

Let’s say you have two lists of NFL teams and you want to quickly see which teams are present in one list but not in the other. This tutorial will walk you through the steps to achieve this using Excel’s built-in functionalities.

To highlight the teams in the first list that are not found in the second list, follow these simple steps:

1. Name Your Ranges: Naming ranges makes formulas easier to understand and manage. Select the range of cells containing your first list (e.g., A1:A18) and in the Name Box (left of the formula bar), type firstList and press Enter. Do the same for your second list (e.g., B1:B20), naming it secondList.

2. Select the First List: Choose the range of cells that constitutes your first list (A1:A18 in our example). This is where the conditional formatting will be applied.

3. Open Conditional Formatting: Navigate to the “Home” tab on the Excel ribbon. In the “Styles” group, click on “Conditional Formatting”.

4. Create a New Rule: From the dropdown menu, select “New Rule”. This will open the “New Formatting Rule” dialog box.

5. Use a Formula: In the “Select a Rule Type” section, choose “Use a formula to determine which cells to format”. This option allows you to use Excel formulas to define your conditional formatting rules.

6. Enter the COUNTIF Formula: In the “Format values where this formula is true” box, enter the following formula: =COUNTIF(secondList,A1)=0.

7. Choose Formatting and Apply: Click the “Format” button to select your desired formatting style (e.g., fill color, font style). Choose a style to visually highlight the differences and click “OK” in both the “Format Cells” and “New Formatting Rule” dialog boxes.

   Result: Excel will now highlight the teams in your first list that are not present in the second list. In our example, “Miami Dolphins” and “Tennessee Titans” would be highlighted.

   Formula Explanation: The formula =COUNTIF(secondList,A1)=0 works as follows:

   • COUNTIF(secondList,A1): This part counts how many times the value in cell A1 (the first team in your first list) appears within the secondList range.
   • =0: This checks if the count returned by COUNTIF is equal to zero. If it is zero, it means the team from cell A1 is not found in the secondList.
   • If the formula evaluates to TRUE (meaning the team is not in the second list), the conditional formatting is applied to cell A1.

   Excel intelligently applies this rule to all cells in your selected range (A1:A18). For example, for cell A2, the formula automatically adjusts to =COUNTIF(secondList,A2)=0, and so on.

8. Compare the Second List (Optional): To also highlight teams in the second list that are not in the first list, repeat steps 2-7. This time, select the range B1:B20 (your second list), create a new conditional formatting rule, and use the formula =COUNTIF(firstList,B1)=0. Choose a different formatting style (e.g., orange fill) to distinguish the lists.

   Result: Now, both lists are conditionally formatted. Teams unique to the second list, such as “Denver Broncos”, “Arizona Cardinals”, “Minnesota Vikings”, and “Pittsburgh Steelers”, will be highlighted with the orange fill (or your chosen format).

By using conditional formatting with the COUNTIF function, you can efficiently compare two lists in Excel and quickly identify the differences between them. This method saves time and reduces the chances of manual errors, making your data analysis more effective.