Microsoft Excel provides two different methods for you to compare two lists and highlight the missing items in each list. You can choose to highlight the missing items in both lists or in one list. We’ll show you how.
RELATED: How to find the percentage difference between values in Excel?
The Fast Way: Highlight Unique Cells to Compare Lists
A quick way to compare two lists in your spreadsheet is to use Excel’s unique highlighting feature. This function highlights the items in a list that are not in the other list. This way you know exactly which items are still missing from your lists.
To use the method, first select the lists you want to compare in your spreadsheet.
With your lists highlighted, click the tab in the Excel ribbon at the top “Start”†
Click on the tab “Start” in the section “Styles” Click Conditional Formatting > Highlight Cell Rules > Duplicate Values.
Excel highlights the missing items in your lists. For example, your first list highlights the items that are missing from the second list, and so on.
And that’s it.
The formula: use conditional formatting to compare lists
The above method highlights items in both lists. To highlight only missing items in a specific list, use a conditional formatting formula as explained below.
RELATED: Use conditional formatting to find duplicate data in Excel
First, in your spreadsheet, select all the rows from your first list. Then click the text box in the top left corner, type FirstListand press Enter. This assigns a unique name to your range of cells so that you can refer to all of these cells with one name.
Assign a unique name to your second list by first selecting all the rows of your list. Then click the text box in the top left corner, type SecondListand press Enter.
Select all the rows of your first list by clicking the text box in the top left corner and choosing ‘FirstList’.
In the Excel ribbon at the top, click on the tab “Start” and choose Conditional Formatting > New Rule.
Back in the spreadsheet, Excel has highlighted the items in your first list that are missing from the second list. Your job is done.
And this way you can quickly make a comparison between two different lists in your Excel spreadsheets. Very useful!
You can also alphabetize your data to manually find the differences in two lists in your spreadsheets. Check out our guide to learn how to do that.
RELATED: Alphabetical data in Microsoft Excel