Suppose we have some data for an employee list that will continually be updated and we want to make sure that any user adding to the list doesn’t add a duplicate entry. In this post we’ll learn how to use data validation to prevent a user from entering a value if it’s already been entered in the table. In our example we have a simple set of data that contains a field for employee ID, name and address and we will prevent any duplicated employee ID’s being entered. Our data has also been turned into a table and the table has been named EmployeeData. If you want to read how to create a table in Excel, please read this Introduction To Excel Tables.
Add a custom validation criteria. This will allow us to write a formula to test whether the data is valid or not. In this case we will enter a formula to count how many times the value appears in the list and limit it to 1.
We can also add a warning message to users that will display when the active cell cursor is on a cell with this data validation.
We can also add a error message that will display if the user does try to input a duplicate value.
