ThreeWave Preventing Duplicates In Data Entry

This page describes how to use Data Validation to prevent duplicate data entry in a range.
ShortFadeBar

In many data entry tasks, you may wish to prevent the user from entering duplicate entries, entering a value that already exists in a list. This is easily accomplished in Excel 97 and later with the Data Validation tool. When you apply Validation to a cell or range of cells, you can restrict what sort of data the user is allowed to enter. A useful facet of Validation is the "Custom" type that allows you to write a formula to test data entry. If the formula returns True (or any non-zero number) the data is allowed in the cell. If the formula returns False (or 0), the data is rejected and an error message is displayed to the user. Note that Validation works only on direct user input. It does not work with data that is pasted in to a range, values that are the result of calculations, or cell modification by Visual Basic code.

To implement no-duplicates validation, select the range of cells that you wish to restrict, and chose Validation from the Data menu. Choose Custom from the Allow list, and enter the following formula:

=COUNTIF($A$1:$A$50,A1)=1

Of course, change $A$1:$A$50 to your range (but keep the '$' characters for absolute referencing). An example Validation dialog is shown below:

Data Validation

 

This page last updated: 14-July-2007