Comparing data isn’t always a common task, but in Excel, it’s an easy task. Take the two lists in the following sheet. The two lists are similar but there are a few differences. In such a short list, you can visually spot those differences but you won’t want to perform a visual check when there’s a lot of data—you’ll miss stuff.

Best online Microsoft MCTS Training, Microsoft MCITP Training at certkingdom.com

Using conditional formatting, you can quickly identify differences between two lists using an expression in the form:

=COUNTIF(otherlist,firstcellincurrentlist) = 0

This technique is simpler if you work with ranges. For purposes of this example, the list in column A is named ListOne and the list in column B is ListTwo.

Now, let’s identify the differences between the two lists, starting with the list in column A:

1. Select ListOne (A1:A10).
2. Click the Home tab, click Conditional Formatting in the Styles group, and choose New Rule from the dropdown list. In Excel 2003, Conditional Formatting is on the Format menu.
3. From the Select A Rule Type list, choose the Use A Formula to Determine Which Cells To Format option. (This step isn’t necessary in Word 2003.)
4. Enter =COUNTIF(ListTwo,A1)=0 in the Formula field.
5. Click Format, click the Fill tab, choose a color, and click OK.
6. Click OK. Any cell in ListOne that contains a value that is not in ListTwo (column B) is quickly identified by its contrasting fill color.

Now, let’s use the same technique to format ListTwo. First, select ListTwo (B1:B10). Repeats steps 2 through 6. In step 4, enter the expression =COUNTIF(ListOne,B1)=0. In step 5, you can choose the same color or a different color. This time, the conditional format identifies entries in ListTwo that aren’t in ListOne.

This technique works with values as well as text entries. The list don’t have to be the same number of entries either. In this example they do, but the technique will work with different-sized lists.