## Highlight duplicate records

*Article last 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

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

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

Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]

Highlight records – multiple criteria [OR logic]

This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in […]

Highlight duplicates in a filtered Excel defined table

You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want […]

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