Highlight Repetitive Data

 

Building on the theme of Conditional Formatting that was discussed last week, there are several instances where having Repetitive Data (identical data entered more than once) into a database is Undesirable. For instance, if you were an HR Manager, you may wish to have the names of employees entered Only Once in a company database.

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!