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.
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.
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 firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.