Sometimes is is not enough to just highlight duplicate values using conditional formatting in Excel, sometimes you just do not want to allow users to enter duplicates. For example if you are collecting customer numbers which are unique and duplicates would mean that the customer list is incorrect.
It is easy to prevent users entering duplicates with a simple formula within Data Validation.
- Highlight the cells you want to apply the validation to (in our exmaple it is A2 to A10)
- Data Tab – Data Tools Group- Data Validation – Settings – Custom
This brings us to the area where we can specify a formula to prevent data duplicates
We use the COUNTIF function which has two parts to it.
range – the range of cells we want to count
criteria – this can be a number, expression, cell reference, or text string that determines which cells will be counted.
The formula looks like this
The formula counts the number of cell in our range $A$2:$A$10 that are equal to the value in A2. (we have made the range of cells A2:A10 absolute or fixed using the $ sign.
By adding the =1 we are specifying they can occur only once. Because we selected the range A2:A20 before we clicked on Data Validation, Excel automatically copies the formula to the other cells.
Let’s test it.
It works!. The user cannot enter a duplicate account number.
Extra Tip- to enter an input message and error alert message, go to the Input Message and Error Alert tab.
WANT TO WATCH THE VIDEO?
- Use TRIM for a speedy data clean up
- Create A Micro Chart In Excel
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
- Create A Timestamp In Excel With Formulas
