Overview

There are two lists in this example:

Sheet: List 1

Sheet: List 2

Create named ranges

1. Select A2:A13 on sheet "List 2"
2. Type Year in Name Box
3. Press Enter

Repeat with remaining ranges:
Sheet: List 2 , Range:B2:B13, Name: Asset
Sheet: List 2 , Range:C2:C13, Name: Cost
Sheet: List 1 , Range:A2:A11, Name: Year1
Sheet: List 1 , Range:B2:B11, Name: Asset1
Sheet: List 1 , Range:C2:C11, Name: Cost1

Why use named ranges? The conditional formatting formula does not work with cell references outside the current sheet.

Highlight records existing in only one list, excel 2007

How to apply conditional formatting formula:

Sheet: List 1

1. Select cells A2:C11 (Sheet: List 1)
2. Click "Home" tab
3. Click "Conditional Formatting" button
4. Click "New Rule.."
5. Click "Use a formula to determine which cells to format"
6. Type =COUNTIFS(Year, $A2, Asset, $B2, Cost, $C2)=0 in "Format values where this formula is TRUE" window.
7. Click "Format.." button
8. Click "Fill" tab
9. Select a color for highlighting cells.
10. Click "Ok"
11. Click "Ok"
12. Click "Ok"

Sheet: List 2

1. Select cells A2:C13 (Sheet: List 2)
2. Click "Home" tab
3. Click "Conditional Formatting" button
4. Click "New Rule.."
5. Click "Use a formula to determine which cells to format"
6. Type =COUNTIFS(Year1, $A2, Asset1, $B2, Cost1, $C2)=0 in "Format values where this formula is TRUE" window.
7. Click "Format.." button
8. Click "Fill" tab
9. Select a color for highlighting cells.
10. Click "Ok"
11. Click "Ok"
12. Click "Ok"

Highlight records existing in only one list, excel 2003

Sheet: List 1

Conditional formatting formula:

Sheet: List 2

Conditional formatting formula:

Sheet: List 1

Sheet: List 2

Download excel sample file for this tutorial.

Compare-lists Highlight records existing in only one list.xlsx
(Excel 2007 Workbook *.xlsx)

Recommended blog post

Filter unique distinct row records
Filter duplicate rows in excel 2007
Highlight duplicate rows in excel 2007

Functions used in article:

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition