How to prevent duplicate entries in a column in Excel?
As we all known, Data Validation is a very useful feature in Excel, you can use it to create drop down list in a cell and limit the values user can enter. But sometimes you may want to prevent users from entering duplicate values in a range of worksheet. How could you avoid duplicate entries in Excel?
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
For example, I am entering identify card number into a range of cells (A1:A20), and I want to make sure all item numbers entered in range A1:A20 are unique. To prevent duplicates, follow these steps:
1. Select the range of cells that will contain the item numbers.
2. Go to Data > Data Validation > Data Validation. See screenshot:
3. And a Data Validation dialog box will display. Click the Settings tab, then click drop down list under Allow, choose Custom, and then enter this formula “=COUNTIF($A$1:$A$20,A1)=1” into the Formula box. See screenshot:
4. And then click Error Alert tab, under the Title box, enter “Duplicate Entry”, and enter the proper message in the Error message box, such as “The value was already entered. All Item Numbers must be unique. Please try again.”
5. Click OK to finish it.
Now when you enter a duplicate number, Excel will prevent the entry and alert the user with an error message, it will show up like this:
It is somewhat difficult for us to use Data Validation function to prevent duplicate entries, now I will introduce you an easy and quick way to solve this task.
1. Select the range that you want to prevent duplicate entries.
2. Click Kutools > Prevent Duplicate Entries, see screenshot:
Now when you enter the same data with the first data in the range, you will get the following warning:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 27 days agonot working properly. only work in manual entry if i use "CTRL+D" then it allows duplicate entry.
To post as a guest, your comment is unpublished.· 1 years agoi want to select the name of workers to whom duty allotted to a particular place what excel formula is to be used to find if i use vlook formula is shows the last worker repeatedly
To post as a guest, your comment is unpublished.· 1 years agoI have same problem like some other users do. I used this formula, but now i can't enter any value. What might be wrong?
To post as a guest, your comment is unpublished.· 1 years agoI used your formula to restrict name duplications. But then it is giving warning to whatever name I enter. Maybe it is detecting by per alphabet instead of the whole name? How can I solve this problem?
To post as a guest, your comment is unpublished.· 1 years agoI am currently using this to not allow duplicates for a form of a schedule. However there are some exceptions I need to allow to be duplicated. Is there a way to allow certain values or in my case text to be duplicated while everything else is not allowed?