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.
- Select the full column of data which we’re going to limit to unique values.
- Go to the Data tab.
- Press the Data Validation button.
- Select Data Validation from the drop down menu.
- Select the Settings tab.
- In the Allow drop down select Custom.
- In the Formula area input this formula.
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.
- Go to the Input Message tab.
- Enter a Title for the input message.
- Enter a descriptive message that will warn the user about the data validation for unique values.
- When the active cell is in the range with this unique value data validation, the input message will display.
We can also add a error message that will display if the user does try to input a duplicate value.
- Go to the Error Alert tab.
- Select the style of pop up message, either Stop, Warning or Information.
- Enter a Title for the pop up message.
- Enter a descriptive message that will warn the user about their error.
- Press the OK button.
- Now if you try to enter a duplicate value, a pop up message box will alert you about your error of entering a duplicate value.