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 through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.
* You will also get a weekly newsletter, unsubscribe anytime!