Author: Oscar Cronquist Article last updated on April 19, 2021

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

1. Conditional Formatting formula - Excel 2007 and later versions

Conditional formatting formula:

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

Back to top

2. Conditional Formatting formula - Excel 2003 and previous versions

The COUNTIFS function was introduced in Excel 2007, here is a formula for previous versions:

=SUMPRODUCT(COUNTIF($B3, $B$3:$B3)*COUNTIF($C3, $C$3:$C3)*COUNTIF($D3, $D$3:$D3))>1

Back to top

3. How to create a conditional formatting formula

  1. Select the cell range you want to highlight duplicate rows for.
  2. Go to the "Home" tab on the ribbon.
  3. Press with left mouse button on the "Conditional Formatting" button. A popup menu appears.
  4. Press with left mouse button on "New Rule.." on the popup menu.
  5. Press with left mouse button on "Use a formula to determine which cells to format", see the image below.
  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. Press with left mouse button on "Format.." button
  8. Press with left mouse button on "Fill" tab
  9. Select a color for highlighting cells.
  10. Press with left mouse button on "Ok"
  11. Press with left mouse button on "Ok"
  12. Press with left mouse button on "Ok"

Back to top

4. How the conditional formatting formula works

Step 1 - Count rows that 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 the next row.

If there are rows that match all three conditions $B3, $C3 and $D3 the COUNTIFS function returns 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 the number is larger than 1

If there is 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.

Back to top

5. 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

Back to top

Recommended blog post

Automatically filter unique distinct row records

Back to top