Wednesday, April 18, 2012
Highlight Repetitive Data
It is useful, therefore, to have a Tool to Identify any such repetitive entries. Using Conditional Formatting with a Clever Formula is one way to accomplish this. Noticing that we are using the above Excel graphic as an example, the following COUNTIF formula, (please note the less-than and greater-than characters that together mean Not Equal To in Excel), can do the trick:
=COUNTIF($A$1:$C$5, A1)< >1
Notice too that the first argument refers to the entire database, and uses an Absolute Reference, whereas the second argument refers to the first cell in the database, and uses a Relative Reference.
Since you are using this formula in a Conditional Formatting statement, you want to have a result of TRUE whenever you want the infringing cell to be highlighted. When the formula returns a TRUE, it activates your chosen formatting.
Clever, eh? Give it a try!