Article updated on January 06, 2018

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

Conditional formatting formula:

=COUNTIFS($B$3:$B$15, $B3, $C$3:$C$15, $C3, $D$3:$D$15, $D3)>1

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($B$3:$B$15,$B3,$C$3:$C$15,$C3,$D$3:$D$15,$D3)>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 formula's cell references changes.

The COUNTIFS function allows you to counts duplicate records if you use the right arguments.

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