The Formula Way: Use Conditional Formatting to Compare Lists

The above method highlights items in both your lists. If you’d only like to highlight missing items in a specific list, then use a formula with conditional formatting as explained below.

RELATED: How to Use Conditional Formatting to Find Duplicate Data in Excel

First, in your spreadsheet, select all rows of your first list. Then, in the top-left corner, click the text box, type FirstList, and press Enter. This assigns a unique name to your range of cells, which lets you refer to all these cells using a single name.

Assign a unique name to your second list by first selecting all rows of your list. Then, in the top-left corner, click the text box, type SecondList, and press Enter.

Select all rows of your first list by clicking the text box in the top-left corner and choosing “FirstList.”

In Excel’s ribbon at the top, click the “Home” tab and choose Conditional Formatting > New Rule.

On the “New Formatting Rule” window, you’ll specify how your missing items will be highlighted. On this window, from the “Select a Rule Type” section, choose “Use a Formula to Determine Which Cells to Format.”

In the “Format Values Where This Formula is True” box, type the following:

=COUNTIF(SecondList,A1)=0

Select the “Format” button and specify how you’d like to format the missing items in your list. Then, save your changes by clicking “OK.”

Back on the spreadsheet, Excel has highlighted the items in your first list that are missing from the second list. Your job is done.

And that’s how you quickly perform a comparison of two different lists in your Excel spreadsheets.