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

Comments  

Permalink +6 Ravikiran
Good and useful tips.
2013-10-26 06:50 Reply Reply with quote Quote
Permalink +1 Sharad
Usefull tips ! Thanks for sharing
2013-11-26 04:49 Reply Reply with quote Quote
Permalink +1 SAM
Thank you so much :)
2013-12-17 10:44 Reply Reply with quote Quote
Permalink +5 BAD
Duplicate entry still allowed for copy and paste.
2014-01-07 06:31 Reply Reply with quote Quote
Permalink +2 Sameer Awais
This formula is okay but when I'm write same word they show me Duplicate Entry & if i 2 same word fill mean Copy paste (Ctrl+C/Ctrl V) 2 same word then can't show me Duplicate Entry Erorr
2014-01-21 14:54 Reply Reply with quote Quote
Permalink -1 Admin_jay
Quoting Sameer Awais:
This formula is okay but when I'm write same word they show me Duplicate Entry & if i 2 same word fill mean Copy paste (Ctrl+C/Ctrl V) 2 same word then can't show me Duplicate Entry Erorr

:roll: It cannot prevent duplicate entries from entering as Copy and Paste.
2014-01-22 01:29 Reply Reply with quote Quote
Permalink 0 Muhammad Naveed
Thanx for this useful formula
2014-02-10 10:27 Reply Reply with quote Quote
Permalink 0 Luis
Thanks!!! save my day...
2014-02-25 21:55 Reply Reply with quote Quote
Permalink +2 VENKATESH
WHEN I COPY PASTE THE DATA IT NOT WORKING ... PLEASE HELP ME
2014-06-28 10:02 Reply Reply with quote Quote
Permalink 0 ATIQ
Thank You Very Much "For This Formula"
2014-07-09 06:48 Reply Reply with quote Quote
Permalink +1 mohamed
thank you, nice information to study
2014-07-21 08:02 Reply Reply with quote Quote
Permalink +1 tester
This formula for some reason doesn't work for named range in Excel 2013. In example, I have a whole A column but the header, i.e. $A$2:$A$1048576 , named as 'id'. The formula =COUNTIF(id,A2) =1 correctly gives TRUE or FALSE when used in other cell for test, but it doesn't affect the data validation - it allows duplicate entries. However when used directly =COUNTIF($A$2:$ A$1048576;A2)=1 , it works for validation as well, giving exactly the same results in other cells.
2014-08-01 14:16 Reply Reply with quote Quote
Permalink 0 Shlok
Nice Tips.... Keep Updating....!
2014-08-29 05:04 Reply Reply with quote Quote
Permalink +1 maryc
I have done these steps, however... no matter what I put into the column it says it is a duplicate even thou it is not.

I have a column that has a header of "P.O.#" I want to prevent from billing more than once for a po.
2014-10-07 14:10 Reply Reply with quote Quote
Permalink +2 Jimmy
It did not work at first but after I unchecked the "ignore blank" box next to custom it worked. Very helpful. Thank you.
2014-10-09 21:07 Reply Reply with quote Quote
Permalink +1 Rajendra Singh Rana
thank you sir, very useful and appreciable :-) .
2015-04-06 06:38 Reply Reply with quote Quote
Permalink +1 shashi dubey
Quoting Rajendra Singh Rana:
thank you sir, very useful and appreciable :-) .

I can't entry any data after applying this method ...
2015-07-07 08:41 Reply Reply with quote Quote
Permalink 0 shital
122
314
441
EXL/112/15-16
264
EXL/113/15-16
EXL/314/15-16RY
HOW CAN AVOID THIS KIND OF DUPLICATE VALUES DURING NEW ENT
2015-08-20 05:42 Reply Reply with quote Quote
Permalink 0 shital
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
2015-08-20 05:43 Reply Reply with quote Quote
Permalink 0 Harikrishna
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.
2015-12-21 22:09 Reply Reply with quote Quote
Permalink 0 Joseph
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!!!
2016-04-15 13:23 Reply Reply with quote Quote

Add comment


Security code
Refresh