Prevent Duplicate Entries in a Range with Excel Data Validation

In this article, we will learn how to prevent duplicate entries in a range with Data Validation.

We will learn how helpful and useful this feature is and its uses to avoid any typing errors or mistakes. It not only prevent users from entering incorrect data in the list but also saves a lot of time. 

Let’s take an example that how we can prevent the user from entering duplicate values in the range:-

As we can see in above image, we have a column C, where we have few products and we’ll enter more Products in that range. We want Excel to prompt us with a message, like “Duplicate Product”, the moment we enter any duplicate entry in the range or “This product has already been added to the list”. So, kindly enter another product, or in other words, we just want the cell to be restricted to have only the unique entries in the range.

And, to prevent duplicate entries in the range, we will simply follow 3 easy steps.

• First, we will select the entire column C
• Go to Data Validation or press “ALT + D + L” to have the data validation dialog box

• Select “Custom” from allow category as we are going to have a function in “formula bar” to prevent users from entering duplicate entries
• Enter the COUNTIF function :- =COUNTIF(C:C,C1)=1

• So, we skip input message and directly jump on “Error Alert” tab
• We will let “STOP” be the error style, and then in the title, we’ll write “Duplicate / Invalid Entry!”
• Then, in the error message box, we type “You have entered a product that already exists in this column. So, please enter some other product that is unique.”

• Click on Ok

Now, we can see there are already five products in the range. So, we enter “Product 06” in cell A14 and the cell accepts the new product without any error because “Product 05” did not exist anywhere in the range.

And now, we try to enter “Product 04” in cell A145 and hit enter. Here, we get an error message which we set for duplicate entries. That’s because, if you look at the range, “Product 04” already exists in the cell A12.

This validation criterion applies to the entire range. So, even if we go and enter any duplicate value in the cell C1, it will not accept and display the same customize error message which we have set.

So, it simply means if a user will attempt to enter duplicate value anywhere in the range, it will be rejected and an error message dialog box will pop up. You can click on “Retry” or “Cancel” to clear the error message and enter the unique product code to the list.

This is how you can prevent users from entering duplicate entries in the range, using Data validation in Excel.

Video: Prevent Duplicate Entries in a Range with Excel Data Validation

Click on the video link for quick reference to the use of it. Subscribe to our new channel and keep learning with us!

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com