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