Author: Oscar Cronquist Article last updated on January 09, 2019

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 create a 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

Step 1 - Count rows which match the current row

The COUNTIFS function allows you to counts duplicate records, make sure you create a condition for each column in your record. There are three columns in this example, the first argument is $B$3:$B$15 and doesn't change when the conditional formatting moves on to the next row.

The second argument is $B3 and is locked to column B, however, the row is a relative cell reference (not locked) and changes when the CF moves to next row.

If there are rows that match all three conditions $B3, $C3 and $D3 the COUNTIFS function return the number of rows that match.

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

returns 1 because the first row is counted as well.

Step 2 - Check if number is larger than 1

If there are more than one row matching we know the record is a duplicate.

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

becomes

1>1

and returns FALSE. Cell B3 is not highlighted.

Do not highlight the first duplicate

The following CF formula highlights duplicates except the first instance, see image above.

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

Recommended blog post

Automatically filter unique distinct row records

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!