## Compare two lists of data: Highlight common records

### 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

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

Compare two columns and highlight values in common

A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]

Compare two columns and highlight differences

A conditional formatting formula highlights values that only exist in one column. Example, BB and GG exist only in column […]

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

Compare two tables: Remove common records

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

Highlight records – multiple criteria [OR logic]

This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in […]

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 comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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)