Preventing Duplicates In Data Entry
This page describes how to use Data Validation to prevent
duplicate data entry in a range.
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:
This page last updated:
14-July-2007