A very common task is a list of lists in Excel to know the similarities or differences between both lists. Although you have already published other articles on this topic, in this section you use the Conditional Formatting to make the comparison.

Compare two lists with conditional formatting

Let’s assume the following lists in Excel:

How to compare two lists in Excel

The first example will be known the elements of List 2 that are present in List 1 and for the following steps:

1. Select the data range B2: B11
2. On the Home tab, click the Conditional Format button and select the New rule option.
3. Within the list of rule types we select the Use a formula that determines the cells to apply formatting option.
4. In the formula box we put the following: = COINCIDIR (B2, \$ A \$ 2: \$ A \$ 11, 0)> 0
5. We click the Format button and select a fill color.

At the moment of applying this rule of conditional format we obtain the following result:

How to compare two columns in Excel

You can see that the cells in List 2 that have the green background are just those values ​​that are also present in List 1. It is obvious that cells that do not have the green background are those that are not part of the List1 however we are going to make the example of that particular case.

Our second example is to know the elements of List 2 that are not in List 1. In this second example we follow the same steps as in the previous example but use the following formula: = ESERROR (B2, \$ A \$ 2: \$ A \$ 11,0)).

Examples compare lists in Excel

Note the result of applying this conditional format rule:

Compare two tables in Excel

In this second example, the cells that have the colored background are those values ​​in List 2 that are not present in List 1. With both examples we have analyzed the two possible cases: find the values ​​in List 2 that are similar to List 1 and also find those that are different.

If you want to make the comparisons with reference to List1 the steps will be the same as in the previous examples except that we will start by selecting the range of List 1 and that we must change the formulas used in the conditional format rule as follows:

To find List 1 values ​​that are present in List 2: = COINCIDIR (A2, \$ B \$ 2: \$ B \$ 11, 0)> 0
To find values ​​in List 1 that are not in List 2: = ESERROR (COINCIDIR (A2, \$ B \$ 2: \$ B \$ 11, 0))
Compare lists in Excel

Compare two lists using formulas

Another method I have previously presented to compare two lists in Excel is to use only Excel formulas. To know more detail of this method consult the article Compare lists in Excel.

Compare two lists with data consolidation

A third method you can use to compare two lists in Excel is through data consolidation. The advantage of this method is that you can not only compare two lists but you can compare three or more lists. Read all the details about this method in the article Compare lists consolidating data.

Any of the three methods I have presented today will help you compare two lists in Excel and you will easily know their similarities and differences.