## Compare two lists of data: Highlight common records

*Article updated on March 12, 2018*

### Overview

In this blog post you will learn how to:

- Create a countifs function
- Create a conditional formatting formula and reference cells outside current sheet
- Highlight common records in two lists

Sheet: List 1

Sheet: List 2

### Create named ranges excel 2007

When constructing a conditional formatting formula you can only reference cells on current sheet. But there is a workaround. The answer is named ranges.

Here is how to create named ranges for this example:

- Click tab "Formulas" on the ribbon
- Click "Named ranges" button
- Click "New..." button
- Type Year in Name field
- Select range $A$2:$A$13 in sheet List 2
- Click OK button
- Click Close button

Repeat process with remaining ranges.

$A$2:$A$13 - Year

$B$2:$B$13 - Asset

$B$2:$B$13 - Cost

### Setup COUNTIFS function Excel 2007

The countifs function looks for duplicate records in the second list (List 2).

**COUNTIFS(**criteria_range1,criteria1, criteria_range2, criteria2...**)**

Counts the number of cells specified by a given set of conditions or criteria

In this example:

List 1:Column A - List 2: Column A (Year)

List 1:Column B - List 2: Column B (Asset)

List 1:Column C - List 2: Column C (Cost)

COUNTIFS(YEAR, $A2, ASSET, $B2, COST, $C2)

This formula contains absolute and relative cell references. The conditional formatting formula must look for values in matching columns.

$A2 is a cell reference to values in column A. The column ($A) is absolute but the row (2) is relative. When the conditional formatting formula execute the next row, only the row number changes, example $A3.

### Highlight common records from two lists Excel 2007

How to apply conditional formatting formula:

- Select cells A2:C11 (Sheet: List 1)
- Click "Home" tab
- Click "Conditional Formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- Type =COUNTIFS(YEAR, $A2, ASSET, $B2, COST, $C2) in "Format values where this formula is TRUE" window.
- Click "Format.." button
- Click "Fill" tab
- Select a color for highlighting cells.
- Click "Ok"
- Click "Ok"
- Click "Ok"

### Highlight common records from two lists Excel 2003

Conditional formatting formula:

### Download excel sample file for this tutorial.

Compare-lists Highlight common values.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

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

Compare two lists of data: Highlight records occurring in only one list

Overview There are two lists in this example: Sheet: List 1 Sheet: List 2 Create named ranges Select A2:A13 on […]### 2 Responses to “Compare two lists of data: Highlight common records”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Is there a way to do this with two worksheets instead of columns in the same worksheet?

I find myself going through each entry looking for the property ID in one worksheet to highlight it in the second worksheet, that way I know what properties are already registered with the City. However, this takes too much time. VLOOKUP will not work in this instance because it only returns one item that matches.

In my situation, I am looking for a function that will return (highlight) all of the matching property IDs in sheet 2 using the entries in sheet 1.

Any help would be greatly appreciated.

Georgina Ram

Sure, if this is the original conditional formatting formula on sheet "Sheet1":

COUNTIFS($A$2:$A$13, $A2, $B$2:$B$13, $B2, $C$2:$C$13, $C2)

and List 2 is now on sheet "Sheet2" then the formula becomes:

COUNTIFS(Sheet2!$A$2:$A$13, $A2, Sheet2!$B$2:$B$13, $B2, Sheet2!$C$2:$C$13, $C2)