How to Use Conditional Formatting to Compare Lists in Excel

We have proven the usefulness of conditional formatting in Excel on multiple examples.

We will show another cool feature in the example below, which will refer to the comparison of two lists with conditional formatting.

Using Conditional Formatting to Compare Lists

For our example, we will use the list of NBA players that played for the Los Angeles Lakers in the 2020/2021 season and the ones that will play the upcoming season (2021/2022).

Graphical user interface, table

Description automatically generated with medium confidence

If we want to highlight only those players that are on the roster for the upcoming season and who were a part of the team a year before, we need to select the desired range (C2:C16) go to Home tab >> Conditional Formatting >> New Rule:

Graphical user interface, application

Description automatically generated

To achieve what we want, we must follow the following formula:

=COUNTIF(observed list ,first cell in the current list) = 1

So, on the pop-up window that appears, we will select the last option, which is: Use a formula to determine which cells to format, and input the following formula:

=COUNTIF($A$2:$A$20,C2)=1

We will then define that the cells with these cells are filled with the green color:

Graphical user interface, text, application

Description automatically generated

How does this formula work? COUNTIF function will check if the value in our cell C2 can be found in the range that we used for comparison (A2:A20). If this value is found, our formula will return a 1, meaning that our conditional formatting will be triggered.

We are locking our range, as it is always the same, but we do not lock the first value, to encapsulate every cell in the second range.

Finally, we will click OK, and have the following results:

Graphical user interface, text, application, chat or text message

Description automatically generated

If we wanted to highlight those players that are new additions to the team, we would have to use the following formula:

=COUNTIF($A$2:$A$20,C2)=0

We will repeat our steps with this formula as the new rule, and set these players to be highlighted in yellow:

Graphical user interface, text, application, email

Description automatically generated

When we click OK, our table will look like this:

Graphical user interface, text, application, chat or text message

Description automatically generated