This blog post shows you how to easily identify duplicate rows or records in a list.

Conditional formatting formula:

=COUNTIFS($A$1:$A$30, $A1, $B$1:$B$30, $B1, $C$1:$C$30, $C1)>1 + ENTER

How to use conditional formatting formula

  1. Select cells A1:C30
  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($A$1:$A$30, $A1, $B$1:$B$30, $B1, $C$1:$C$30, $C1)>1 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"

How the conditional formatting formula works

The formula contains absolute and relative cell references. In each cell the formulas cell references changes.

In cell A1: COUNTIFS($A$1:$A$30, $A1, $B$1:$B$30, $B1, $C$1:$C$30, $C1)>1

becomes

COUNTIFS({1, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0}, {1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0})

becomes

COUNTIFS({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}) and returns 1.

COUNTIFS({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0})>1 returns False and cell A1 is NOT formatted with a cell color.

In cell A2 the formatting formula changes to: COUNTIFS($A$1:$A$30, $A2, $B$1:$B$30, $B2, $C$1:$C$30, $C2)>1 and returns FALSE and is also NOT formatted with a cell color.

Recommended blog post
Automatically filter unique distinct row records

Download excel sample files for this tutorial.
highlight duplicate records.xlsx
(Excel 2007 Workbook *.xlsx)
highlight-duplicate-records.xls
(Excel 1997-2003 Workbook *.xls)

Functions used in this formula:
COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria