## Highlight duplicate records

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

Highlight duplicate values in a cell range

Question: How do I highlight duplicate values in a range (A1:D4) using conditional formatting? Answer: Conditional formatting formula: =IF(ROW(G5)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, […]

How to highlight duplicate values

The picture above shows duplicate values in column B, only the second or more duplicates are colored and easily identified. […]

Highlight duplicates based on criteria, part 1

Conditional formatting formula: =IF($A2=$E$1, COUNTIFS($A$2:$A$16, $E$1, $B$2:$B$16, $B2), 0) How to apply the conditional formatting formula in excel 2007: Select […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Highlight dates in a date range

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

How to highlight MAX and MIN value based on month

Conditional formatting formula to highlight max value in every month: =B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Conditional formatting formula to highlight min value […]

### 8 Responses to “Highlight duplicate records”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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 […]