## Highlight duplicate rows in excel 2007

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($A$1:$A$30, $A1, $B$1:$B$30, $B1, $C$1:$C$30, $C1)>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 formulas cell references changes.

In cell A1: COUNTIFS($A$1:$A$30, $A1, $B$1:$B$30, $B1, $C$1:$C$30, $C1)>1

becomes

COUNTIFS({1, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0}, {1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0})

becomes

COUNTIFS({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}) and returns 1.

COUNTIFS({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0})>1 returns False and cell A1 is NOT formatted with a cell color.

In cell A2 the formatting formula changes to: COUNTIFS($A$1:$A$30, $A**2**, $B$1:$B$30, $B**2**, $C$1:$C$30, $C**2**)>1 and returns FALSE and is also NOT formatted with a cell color.

### 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

### 7 Responses to “Highlight duplicate rows in excel 2007”

### Leave a Reply

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

<code>your formula</code>

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