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:

  1. Click tab "Formulas" on the ribbon
  2. Click "Named ranges" button
  3. Click "New..." button
  4. Type Year in Name field
  5. Select range $A$2:$A$13 in sheet List 2
  6. Click OK button
  7. 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:

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

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