Highlight duplicates

One of the most common tasks that an Excel user needs to do is to find duplicates on a list of data. There are several ways to do this, depending on the needs. One of the ways is to highlight the values that are duplicated on a column. This can be done using the Format-Conditional Formatting option on the menu bar. Here's an example:

On cell A2, I created a Conditional Formatting with a condition "Formula is" and putted the function =COUNTIF(A:A,A2)>1. The COUNTIF function has the following syntax:


range is the area where you you want to check if it finds the value specified by criteria.

This will return TRUE if the value from cell A2 is found more than 1 time in column A. If this is TRUE it will apply the Format that I defined. I then Copy and Paste Special-Format to the rest of the cells in column A.

In Excel 2007 and 2010, you can use the a new option on the Conditional Formatting menu that is the Duplicate Values.

Just select the range where you want to highlight the duplicate and go to the menu bar and choose Highlight Cells Rules-Duplicate Values. A Duplicate Values dialog box will open. You can choose to highlight the Duplicate or the Unique values and you can set the color that you want to use to highlight the values.
The result will be the same as using the COUNTIF solution. On Excel 2003 you don’t have this Duplicate Values option on the Conditional Formatting menu.

2 comentários:

Cristine Lubs said...

Would it be possible to only highlight duplicates if another cell is also a duplicate? Example: D28 and D29 are duplicates but F28 and F29 are not, therefore no highlight?

Anonymous said...

I need to highlight duplicates based on data in one column, but only if they are within a range of time (5minsof each other) in another column


A = customer number
b = time of entry

1 12345 12:04:56
2 23456 13:17:53
3 34567 12:04:56
4 12345 12:05:36
5 12345 13:09:16

So in this example row 1 and 4 woudl be hoghlighted as they occur within 5 mins of each other but row 5 wouldt as its an hour later.

Help please!