How to Prevent Duplicate values through Data Validation
In this post we will see how we can prevent/stop any range to accept any duplicate values. We all know the power of Data Validation. We can do a lot of stuff through Data validation e.g. we can restrict cells to accept any number of any specific digits, we can assign any specific text length which can be accepted by a particular range, we can create a drop-down list in a cell etc….
Now, to prevent any duplicate values we’ll again knock the door of Data Validation.
Let’s take an example; here we want to enter some employee’s data of a company. The first thing which we need to put in is Employee ID. As we know that Employee ID is unique in nature i.e. a unique Employee ID is assigned to each employee.
Step1: Select the range of cells on which we need to apply the data validation.
Step2: Go to Data Tab -> Data Validation.
Step3: A Data Validation dialog box will appear. In this box under the settings tab -> Allow: -> Custom.
Step4: When we choose custom, just below that we have a Formula input box. Here we will use Countif formula. (Let me just take few words to explain this formula to those who don’t use it, here countif will keep a check on the number of times an entry is coming. If any entry repeats more then once, a message box will appear to show the error). The formula will be written as =COUNTIF($A$2:$A$12,A2)=1
Step5: Now if we just press OK then Data validation will be applied and while entering Employee IDs, if the user put any Employee ID twice he/she will get a default message from Excel but if we want to edit this message as per our requirement, we can go to Error alert tab in Data validation Dialog box.
Step6: Here we can edit the Title of the message in Title box and Error message in Error Message box.
Step7: Now press OK.
You can download the Excel file used for this post here…..