Compare two lists of data: Highlight common records in excel

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Compare > Compare two lists of data: Highlight common records in excel

← Previous post -

Filed in Compare, Excel, Records on Jan.11, 2011. Email This article to a Friend

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:

=SUMPRODUCT(COUNTIF($A2, Year)*COUNTIF($B2, Asset)*COUNTIF($C2, Cost))

Download excel sample file for this tutorial.
Compare-lists Highlight common values.xlsx
(Excel 2007 Workbook *.xlsx)

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 existing in only one list in excel

Compare two lists of data: Filter common row records in excel

Compare two lists of data: Filter records existing in only one list in excel

Highlight common values in two lists using conditional formatting in excel

Filter and highlight duplicate column records in excel 2007