Excel Custom Data Validation To Allow Only Unique Entries And Reject Duplicates

Have you ever been faced with a situation where duplicates keep showing up in your compiled data and you are thinking of a way of restricting your users from inputting duplicates entries in your records thereby saving yourself the time of cleaning them up manually?

We will configure a custom data validation rule to allow only unique entries using the classic COUNTIF formula to identify duplicates.

Let’s say we are in charge of helping the Federal Government compile the list of Nigerians to get the Covid-19 cash support. We definitely do not want duplicate BVNs and duplicate Account Numbers.

The formula we put in is =COUNTIF($C$3:$C$32,C3)<=1

The custom validation criteria allows us to use logical formula to specify what is accepted and what should be rejected. Any entry on which the formula evaluates to TRUE is accepted and when it evaluates to FALSE, that entry is rejected.

We also put a nice Error Alert

If anyone tries to smuggle himself or a family member in twice, the sheet rejects his duplicate entry

And that’s how Excel helps us with a nationally important issue.

Don't forget to check out our premium online courses: https://class.urbizedge.com