Find differences for big dynamic lists in Excel

(for this post some formulas and menu names are in spanish as my excel and computer are in spanish and excel formulas depend on this).

Here is how to find and mark differences in unequal, really long lists or tables in Excel. For my example, one list is a partial list from other. Some items are missing and you’ve to find which ones are.

This is the full list.

full list

This is the partial list where some items are missing.

partial list

Select the whole column with more data and go to Inicio > Formato condicional > Administrar reglas

button selector

Create a new rule, which is going to be applied to the whole column which has more data.

create new conditional format

Set a background colour for it to mark missing fields and write the following formula


$B:$B is the list with missing items. $A1 is the full complete list to compare against.

create new conditional format

Now it should mark the entries that aren’t found in both lists.

full list