- Learn how to use and modify four powerful Excel functions
- Quickly apply AutoFilter to view just the missing values
- Easily manage and compare massive lists of data
For shorter lists, however, there is a much easier, non-formula way to compare two lists in Excel 2007 and later.
How to do it
- Arrange the lists in two columns with List A in column A and List B in column B.
- Highlight both lists.
- Navigate to Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values…
- In the Duplicate Values dialog box, select “Duplicate” from the drop-down list to highlight values (in our example, names) found on both lists and click OK.
It doesn’t get much easier than that. If you’d rather Excel highlight the values missing from either list, select “Unique” from the drop-down list on the Duplicate Values dialog box (step 4 above).
The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!