Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


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?

Prevent duplicate entries with Data Validation feature

Quickly prevent duplicate entries with one click

arrow blue right bubblePrevent duplicate entries with Data Validation feature

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:


arrow blue right bubbleQuickly prevent duplicate entries with one click

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.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now.

With Kutools for Excel, you can prevent duplicate entries with one click. Please do as this:

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

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 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...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Zakarya Rkaa · 3 days ago
    I done this configuration,its result is plus for number and characters but when i inserted the same link of http://www.Facebook.com in second row i didn't faced to any error.
  • To post as a guest, your comment is unpublished.
    guna · 2 months ago
    i 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.
    Justas · 3 months ago
    I 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.
    Li · 4 months ago
    I 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.
    Chris · 6 months ago
    I 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?