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 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
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets 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.· 9 days 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 months 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.· 3 months 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?
To post as a guest, your comment is unpublished.· 7 months agoHow i do the opposite?
i have a column of data and want to warn the user if they are entering a NEW item number that doesn't match any previous enteries
To post as a guest, your comment is unpublished.· 7 months ago[quote name="GARY"]How i do the opposite?
i have a column of data and want to warn the user if they are entering a NEW item number that doesn't match any previous enteries[/quote]
As you have mentioned that user is allowed only to enter duplicate data not unique and there are some entries available within particular range.
Plz try this....
To post as a guest, your comment is unpublished.· 9 months agothanks alot for the tips, but need your more help as i am working on sheet where i have to record the name of the trainees of the year 2015, so not to train the same trainess again 2017 i have to design the database which shows me the duplicate name by highlighting it, so please help me with it.