Comparing Two Lists With Conditional Formatting

Category: Formatting / Formulas | [Item URL]

Excel's Conditional Formatting feature has many uses. Suppose you need to compare two lists, and identify the items that are different. The figure below shows an example. These lists happen to contain text, but this technique also works with numeric data.

The first list is in A2:B19, and this range is named OldList. The second list is in D2:E19, and the range is named NewList. The ranges were named using the Insert - Name - Define command. Naming the ranges is not necessary, but it makes them easier to work with.

As you can see, items in OldList that do not appear in NewList are highlighted with a yellow background. Items in NewList that do not appear in OldList are highlighted with a green background. These colors are the result of Conditional Formatting.

How to do it

  1. Start by selecting the OldList range.
  2. Choose Format - Conditional Formatting
  3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
  4. Enter this formula:
    =COUNTIF(NewList,A2)=0
  5. Click the Format button and specify the formatting to apply when the condition is true (a yellow background in this example).
  6. Click OK

The cells in the NewList range will use a similar conditional formatting formula.

  1. Select the NewList range.
  2. Choose Format - Conditional Formatting
  3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
  4. Enter this formula:
    =COUNTIF(OldList,D2)=0
  5. Click the Format button and specify the formatting to apply when the condition is true (a green background in this example).
  6. Click OK

Both of these conditional formatting formulas use the COUNTIF function. This function counts the number of times a particular value appears in a range. If the formula returns 0, it means that the item does not appear in the range. Therefore, the conditional formatting kicks in and the cell's background color is changed.

The cell reference in the COUNTIF function should always be the upper left cell of the selected range.



Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy