## Very easy: Compare Two Lists in Excel

Forum to talk about all the latest technologies - ins and outs!!
Posts: 292
Joined: Tue Sep 25, 2007 12:41 pm

### Very easy: Compare Two Lists in Excel

This tutorial will show you how to compare two lists using Excel's conditional formatting.

Suppose you have two lists of top European football clubs - one you prepared, the other one your friend. You will check if both the lists contain all the top clubs. (Remember, it's just an example – your favorite club may not be in the list )

Before, we proceed let's name our club lists.

select the range A2:A17 > on the Formulas tab > click on Define Name> Enter ListA > click OK.

Do the same for the range B2:B15 and name it ListB.

So what I want to do?

First, I want to check how many clubs from my list (ListA) are missing in my friend's list (ListB). So how do I proceed?

Select the range for ListA (A2:A17).

On the Home tab > click Conditional Formatting > click New Rule...

The 'New Formatting Rule' window will open

Here select > 'Use a formula to determine which cells to format' > enter the formula =COUNTIF(ListB;A2)=0

Here next to Preview you have the Format... button > click on it to select a Fill background color for the cells in ListA to be marked as missing in ListB. I have selected light violett color > click OK.

So now in ListA cells (or othe way, clubs) are marked that are still not included in ListB.

Do the Same for ListB

That means mark or find out the football clubs that are listed in ListB but still not in ListA.

Select the range B2:B15

On the Home tab > click Conditional Formatting > click New Rule...

The 'New Formatting Rule' window will open

Here select > 'Use a formula to determine which cells to format' > enter the formula =COUNTIF(ListA;B2)=0

Here next to Preview you have the Format... button > click on it to select a Fill background color for the cells in ListB to be marked as missing in ListA > click OK.

So the table below showing you clubs in ListB (marked light blue) that are missing in ListA.

So what does COUNTIF function do?

As you see, under conditional formatting I have used the COUNTIF function. It takes two arguments - a range, and a criteria. So its syntax is

COUNTIF(range, criteria)

It counts the number of cells (in a given range) to find if they meet your specified criteria. If COUNTIF returns a 0 (zero), that means the criteria you mentioned is not found within the specified range.

So when I enter =COUNTIF(ListB;A2)=0 it counts all the cells (in this case, for club name) in ListB that are euqal to cell A2 (or club FC Barcelona), and when it doesn't find FC Barcelona on ListB, it marks cell A2 with light violett color.

You may ask, well here I put only cell A2 as my criteria, what about other cells or club names like A3, A4, A5........A17? How they are compared?

The thing is, you have selected the complete range A2:A17 before you went to conditional formatting – that means, first it looks for the criteria A2 (if it is present in ListB), if not then mark the cell with light violett color, then look for cell A3 (or club Real Madrid CF). Real Madrid CF is present in ListB, so the cell A3 is left as it is (not marked)..........and so the story goes on

By the way, I am using Excel 2007.

Have fun my good friends

*Recommended:

Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.