Hi Excellers, welcome to another #formulafriday blog post in my #Excel 2021 series. Data validation is a great way to control data entry into your Excel spreadsheets. The main positive is to promote data integrity to allow easy data analysis. In this example, I will show you how to ensure that a user does not enter any duplicate customer account numbers into your Excel worksheet. This is useful when creating new customer references or accounts which need to be unique. So, I will demonstrate Data Validation to ensure that customer account numbers are unique. The COUNTIF Function will be used.
Use Data Validation To Prevent Duplicate Data Entry
- Select your range of cells for that you want to apply this validation to- I have used B6:B12
- Data Menu | Data Tools Group | Data Validation
- Select the Settings tab
- In the Allow drop-down list select Custom- this is where we enter our formula
- In the Formula box enter the formula
=COUNTIF($B$6;$B$12,B6)=1
This basic setting will prevent any duplicate entries into the cells B6 to B12. (perform data validation). We can tailor this and fine-tune it for the users to instruct them as to what the problem is with their data entry. To further customise the error alert there are a few further steps.
- Select Error Alert tab
- In the title box, enter Duplicate Entry (or any other gentle warning!)
- In the Error message box, enter “This value has already been used”- or any other warning message you want
- Click Ok.
Now, when a user enters a duplicate Customer ID they will be warned with full instructions. In this example, I have attempted to enter IL009 into cell B12. The data validation takes care of it. It is a really great use of the COUNTIF function.
If you want more Excel and VBA tips like this data validation one, then sign up for my monthly newsletter. I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
