Data validation is a great feature in Excel, and I often use it to create a drop down list in a cell. That helps prevent data entry errors, and limits what people can input.
You can use data validation rules in other ways too. For example, you can prevent duplicate entries in an range or cells, or in a table column.
In this example, employee data is being entered in a formatted table, and each employee must have a unique ID number. The COUNTIF function can check the cells for identical entries, and warn you, or stop you, if an ID number is already in use.
Use COUNTIF to Check a Range of Cells
To create this data validation rule, I named the data cells in the EmpID column as EmpIDs. This step is necessary, because data validation can’t use the table column name directly.
The good news is that named range is based on a formatted Excel table, so it is dynamic — it will automatically grow or shrink if the number of rows changes. In older versions of Excel, you can use a formula to create a dynamic range.
Then, I used that range name, EmpIDs, in a COUNTIF formula.
To prevent duplicates, the count must be 1 or zero, and if not, a warning will appear when you enter a duplicate number.
Add an Error Message
If you enter a duplicate number, the data validation rule will show a default message, telling you to try again, or cancel your entry. You can personalize that message, to help people figure out what they’ve done wrong.
In the screen shot below, the message explains that the entered number is already in use, so you can cancel that, and try a different number.
You can read the detailed instructions on my Contextures website – Prevent Duplicates in an Excel Table – and you can see the steps in the video below.
Video: Prevent Duplicate Entries
This video shows you the steps for creating a formatted table, naming one of the columns, and setting up the data validation.