## Highlight duplicate records

*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:

### How to use conditional formatting formula

- Select cells A1:C30
- Click "Home" tab
- Click "Conditional Formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- 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.
- Click "Format.." button
- Click "Fill" tab
- Select a color for highlighting cells.
- Click "Ok"
- Click "Ok"
- 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

### 8 Responses to “Highlight duplicate records”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

For those wanting to know how to do this in versions of Excel prior to XL2007, here is the Conditional Formatting formula to use. Select the cells in Columns A, B and C from Row 1 down to the last row you want to conditionally format and use this Conditional Formatting formula...

.

.

=SUMPRODUCT(--($A$1:$A$30&"X"&$B$1:$B$30&"X"&$C$1:$C$30=$A1&"X"&$B1&"X"&$C1))>1

.

.

Those embedded X's just need to be a character that is guaranteed not to be in any of the cells being conditionally formatted. These characters ensure no accidental matches occur during the concatenations; for example, without them, an accidental match could occur like this...

"12"&"3"&"4" = "1"&"23"&"4"

both equating to "1234" meaning the equality check would be true; with the X's in place, you get this...

"12"&"X"&"3"&"X"&"4" = "1"&"X"&"23"&"X"&"4"

with the first equating to "12X3X4" and the second equating to "1X23X4" and the equality check would be false.

Here is another conditional formatting formula, excel 2003:

=SUMPRODUCT(COUNTIF($A1, $A$1:$A$30)*COUNTIF($B1, $B$1:$B$30)*COUNTIF($C1, $C$1:$C$30))>1

this formula is not working for me.....

Deepak,

what does your formula look like?

Remember, you must understand how relative and absolute cell references work.

[...] Here is a post where I use this technique: Highlight duplicate rows [...]

Love you for this formula. Thank you for posting....

Mohasin,

Thank you for commenting!

[…] Here is a post where I use this technique: Highlight duplicate rows […]