Log in
x
or
x
x
Register
x

or

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:

doc-prevent-duplicate-entries1

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:

doc-prevent-duplicate-entries2

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.”

doc-prevent-duplicate-entries3

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:

doc-prevent-duplicate-entries4


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:

doc-prevent-duplicate-entries5

Now when you enter the same data with the first data in the range, you will get the following warning:

doc-prevent-duplicate-entries6


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.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    GARY · 2 months ago
    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
    • To post as a guest, your comment is unpublished.
      Parwez · 2 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]

      Hi Gary
      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....
      "=countif($A$1:$A$20,A1)>1"
  • To post as a guest, your comment is unpublished.
    Abdul Baseer · 5 months ago
    thanks 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.
    • To post as a guest, your comment is unpublished.
      Parwez · 2 months ago
      Hi Baseer

      Plz select both data 2015 and 2017 and follow as below....
      Home->conditional Formatting ->Highlight Cells Rules->Duplicate Values... Click on OK.
      Duplicate names will be highlighted.
  • To post as a guest, your comment is unpublished.
    Joseph · 1 years ago
    Please Help, How can I put a duplicate entry of values? I want to get a warning once I repeat a number 5 times. NOT ONLY TWICE BUT 5 TIMES. My limit is 5 times. please HELP HELP!!!
    • To post as a guest, your comment is unpublished.
      Parwez · 2 months ago
      Hi Joseph
      Plz try this.....
      "=countif($A$1:$A$20,A1)
    • To post as a guest, your comment is unpublished.
      Parwez · 3 months ago
      Hi Joseph
      I think Your requirement is very simple, plz try this....
      "=countif($A$1:$A$10)
  • To post as a guest, your comment is unpublished.
    Harikrishna · 1 years ago
    unfortunately this formula is not working, it is showing alert when am entering duplicate value, i am using this formula for email id column.
    Please suggest me.
  • To post as a guest, your comment is unpublished.
    shital · 2 years ago
    122
    314
    441
    EXL/112/15-16
    264
    EXL/113/15-16
    EXL/314/15-16
    how can avoid this kind of duplicate values during new entry in excel