How to prevent duplicates in a column in Excel
Today I'll tell you how to prevent duplicates from appearing in a column of your Excel worksheet. This tip works in Microsoft Excel 2016, 2013, 2010 and lower.
We covered a similar topic in one of our previous articles. So you should know how to automatically highlight duplicates in Excel once something has been typed.
This article will help you stop duplicates appearing in one or several columns in your Excel worksheet. So you can have only unique data in the 1st column of your table be there invoice numbers, stock keeping units, or dates, each mentioned only once.
How to stop duplication - 5 easy steps
Excel has Data Validation - one unfairly forgotten tool. With its help you can avoid errors occurring in your records. We will be sure to devote some future articles to this helpful feature. And now, as a warm-up, you will see a simple example of using this option. :)
Suppose, you have a worksheet named "Customers" that includes such columns as Names, Phone numbers, and Emails you use for sending newsletters. Thus all email addresses must be unique. Follow the steps below to avoid sending the same message to one client twice.
- If necessary, find and delete all duplicates from the table. You can first highlight the dupes and delete them manually after looking through the values. Or you can remove all duplicates with the help of the Duplicate Remover add-in.
- Select the entire column where you need to avoid duplicates. Click on the first cell with data keeping the Shift keyboard button pressed and then select the last cell. Or simply use the combination of Ctrl + Shift + End. It is important to select the 1st data cell first.
Note: If your data are in a simple Excel range as opposed to a full-fledged Excel table, you need to select all the cells in your column, even the blank ones, from D2 to D1048576
- Go to Excel "Data" tab and click on the Data Validation icon to open the dialog box.
- On the Settings tab, choose "Custom" from the Allow drop down list and enter
=COUNTIF($D:$D,D2)=1into the Formula box.
Here $D:$D are the addresses of the first and the last cells in your column. Please pay attention to the dollar signs that are used to indicate absolute reference. D2 is the address of the first selected cell, it is not an absolute reference.
With the help of this formula Excel counts the number of occurrences of the D2 value in the range D1:D1048576. If it is mentioned just once, then everything is fine. When the same value appears several times, Excel will show an alert message with the text you specify on the "Error alert" tab.
Tip: You can compare your column with another column to find duplicates. The second column can be on a different worksheet or event workbook. For example, you can compare the current column with the one that contains the blacklisted emails of customers
you don't won't to work with any longer. :) I will give more details about this Data Validation option in one of my future posts. - Switch to the "Error alert" tab, and enter your text into the fields Title and Error message. Excel will show you this text as soon as you try to enter a duplicate entry into the column. Try to type the details that will be accurate and clear for you or your colleagues. Otherwise, in a month or so you can forget what it means.
For example:
Title: "Duplicate email entry"
Message: "You have entered an email address that already exists in this column. Only unique emails are allowed."
- Click OK to close the "Data validation" dialog.
Now when you try to paste an address that already exists in the column, you will see an error message with your text. The rule will work both if you enter a new address into an empty cell for a new customer and if you try to replace an email for the existing client:
If your "No duplicates allowed" rule can have exceptions :)
On the fourth step choose Warning or Information from the Style menu list. The alert message behavior will change correspondingly:
Warning: The buttons on the dialog will turn as Yes / No / Cancel. If you click Yes, the value you enter will be added. Press No or Cancel to get back to editing the cell. No is the default button.
Information: The buttons on the alert message will be Ok and Cancel. If you click Ok (the default one), a duplicate will be added. Cancel will take you back to the editing mode.
You may also be interested in
Table of contents
2019–2010 to do your daily work
98 responses to "How to prevent duplicates in a column in Excel"
Thanks for the nice tip...
I published it on our Excel group for hoping more members will benefit from
http://www.linkedin.com/groupItem?view=&gid=153811&type=member&item=5798383424137875460&trk=groups%2Finclude%2Fitem_snippet-0-b-ttl
Regards,
Ahmad
This does not work for simple text or string values.
any suggestions?
Most likely your Excel spreadsheet contains some leading or trailing spaces, which cause the problem. Try to remove them using our free Trim Spaces add-in. If the problem persists, please send me your workbook for testing: alexander.frolov@ablebits.com
Hi,
Thank so much.
It`s same issue happen with me, i could not use this formula.
Could you please assist.
Regards
alex, I emailed you.
Thanks,
Niall Williams
The steps wont work in my excel sheet. I cant enter value in the cell, the alert keep prompting. Even though, the value is a new value, not duplicating any value.
I use Excel 2007. The data is a combination of characters. E.g. TA001, PB056.
Pls help.
Hello Rina,
Please send me your workbook for testing at alex@ablebits.com and I will try to figure it out.
Hi Alex,
Did you figure out the solution? I am struggling with it too.
I have the same problem too! I use Excel 2010 and 2016. both have the same problem. The formula just wouldn't change to the correct cell, they all direct to the 1st cell. e.g. COUNTIF($N:$N,N1)=1, N2-N200 all show as N1 in the formula. how to solve?
I am having the same problem, what was the solution?
Hi Penny,
I too faced the same issue while using the formula as for example. =countif($D:$D,D2)=1. But after I altered the formula slightly and gave it as countif(D,D2)<=1.(Note : No '=' in before countif).
Doesn't seem to work if the data column to be checked for duplicates is populated via a linked combo box, or other formula. Is there a way around this?
Seems strange, please send you sample workbook to support@ablebits.com
This doesn't work if you copy and paste the value?? How do I change this?
Unfortunately, Data Validation prevents invalid entries only when you type data directly in a cell using the keyboard. The pasted values are not checked for validity.
If you want to block the insertion of values which are already in the list from the clipboard, a special VBA code is needed. You can ask for a macro on http://www.mrexcel.com or http://www.excelforum.com forums.
Please note that a quite complex code will be required to process all the variants.
I have a question, would appreciate if you could help. I am applying the rule to a list of names, however, it doesn't recognise the entire content of the cell ie, if I already have an Adam Smith in my list and I would like to add Adam Jones, it recognises 'Adam' and will prompt an error. Would you be able to advise?
Thanks!
What if I already have a "data validation" for a list in that column, but still need to prevent duplicates, how do I fix it ?
Is there another way without using Data Validation ?
How to prevent duplicate numeric and text entries in excel column, only unique numeric and text data allowed. above formula: example =countif($D:$D,D2)=1 is working for both numeric and text value to prevent duplicate.
My question are below:
1. How to prevent duplicate only Numeric entries in excel column, only Unique Numeric data allowed.
2. As well as how to prevent duplicate only text entries in excel column, only Unique text data allowed.
Kindly do the reply needful.
Regards
Pradeep Kumar
Please specify what result you want to get. This formula works for text as well as for numeric data.
If you want to allow only unique numbers, text can be repeated, then use this formula:
=OR(ISNUMBER(D2)=FALSE, countif($D:$D,D2)=1)
If only unique text is allowed (numbers can duplicate), then the formula will be as follows:
=OR(ISNUMBER(D2)=TRUE, countif($D:$D,D2)=1)
If you want to get something different, please give me more details.
Dear,
Your trick is nice but i m facing problem that if somebody use cut copy paste the trick is gonna fail.
It is working with this formula =OR(ISNUMBER(D2)=TRUE, countif($D:$D,D2)=1) in excel 2010. thank you.
f you want to allow only unique numbers, text can be repeated, then use this formula:
=OR(ISNUMBER(D2)=FALSE, countif($D:$D,D2)=1)
hi, i still can;t get it going with this formula.
Hi ,
I have a series of web URLS in an excel . I want the duplicates to be stopped from being entered . Could you tell me how to do that ..
yes correct but still remain same problem using with copy-paste,
how to useful with copy-paste?
Is day a way to prevent similar entries? What I mean is, I would like an alert to appear if a part of it already appeared before. For example:
I have a table with product specification:
1. coolproduct 17B
2. thing 19C
3. whatever 120
Is there a way for an alert to pop up if I put in "coolproduct 19A" and suggest that a similar entry already exists?
After you've removed the duplicates, if you want to send an email to the list, the emails must be comma separated. You can turn a column into a list using this in-browser tool:
http://convert.town/column-to-comma-separated-list
What is the best way to do the following;
allocating employees to vehicles lets say vehicle number 51. If 51 is selected for more than 4 employees to go to a site an error is generated to say to the user to select another vehicle as this one is full
Thank you.
This was exactly what I was looking for.
Saved me a lot of manual time and effort.
Simple and easy to understand steps for a novice like myself.
I want the whole of the A columns to restrict duplications and have put in the following formula but it does not seem to be working:-
=COUNTIF($A:$A,A1)=1
Is this wrong?
I am inputting just a straight number i.e. 1150600542
as an example number so nothing irregular.
How to avoid duplicate entry in multiple column.
For Example:
COL_A COL_B COL_C COMMENT
JOB_A 1 2 Data is ok
JOB_B 1 2 ok Although there are duplicate data in COL_B and COL_C
COL_A 1 1 ok Although there are duplicate data in COL_A and COL_B
JOB_A 1 2 NOT OK because exacly similar data is there in the first raw.
You provide very good tutorial. thanks.
thanks for your SPAM !
nice toturial.
list of top packers and movers in delhi
I have a similar problem but with multiple criteria. How to identify an entry in an active cell is duplicate or not based on dual matching criteria?
Regards,
A.V.R. Murty
Hi. I havea concatenate formula where I want an error to be shown when there are duplicates in the column. This doesn't seem to work.
Sean
This has helped me a lot. I have a combination of data in 2 columns which must be unique.
Example of valid data
Date | Shift
08 Aug | Night
08 Aug | Day
Example of INVALID data
Date | Shift
08 Aug | Night
08 Aug | Night
So I have to avoid entry of duplicate data in a combination of columns. I am not able to figure it out please help.
It is not working in my excel sheet. please Help me..
It did not work at first but after I unchecked the "ignore blank" box next to custom it worked. Very helpful. Thank you.
I am encoding tons of household ID nos which consists 10 digits-4 digits-5digits. I have to ensure that no repetition of the said HH No. The formula is working just fine. However, when I Copy/Paste the whole HH ID No., the formula is NOT working. Any other way that whenever I copy/paste I get the same result of the used formula? Thank you.
thnx, it war really helpfull!
how can i assign two validation on a single cell
one is for list validation (means the data should be from that range)
second i want to prevent them from repetition
how can i do this ?
P7 to P506 have GR# which are for list
i want to prevent C column to not to repeat and should be from the P column
It works for a new file created "email" colum with "UNIQUE" formula.
I couldn't implement it to an existing file's "email" field after removing duplicates, wanted to convert it's email colum into a unique field, but didn't function.!
Thanking you,,!
AHMED IBRAHIM
DUBAI L,,,
Input
Column A Column B
Grapes Fruits
Grapes Fruits
Tomatto Vegetable
Apple Fruits
Onion Vegetable
Tomatto Vegetable
Apple Fruits
Mango Fruits
Papaya Fruits
Output
Fruits 4
Vegetable 2
Anybody know the answers.
Suppose I want this formula to affect the value in the middle of a column up to a certain point. e.g. from D5577 to D10000. Because I already have some data in that column and want the formula to ignore that data.
Hi,
I am trying to prevent duplicacy by data validation but it is not applicable in case of copy-pase option. what i mean to say is when i copy the cell value and paste somewhere else in the same column it does not show the error message. however it shows when i am typing the same value. in this way my purpose is not fulfilled. plz guide in this regard.
thank you
banashree
I would like to stop duplicate value entry in a self generating list. Any idea how to do it.
I want to make one table which have no duplicate numbers & also it is random .
Requirement :
First input : 1 - 90 for same numbers came in first column
Second input : 1 -90 random no came in 910 columns but not repeat
How do we copy the entire data of my worksheet i'm asking this question because when i select and copy(ctrl+c), it says "That command cannot be used on multiple selections". Please Help
hi,
i have just tried out this formula to one of my spreed sheet and it's great.
However, when i type in a new invoice number on the column I've just selected it keeps picking up as duplicate. Also, how do i remove this format to re-set again? please need assistance asap.
thanks - Vavine Pianta
after failed many times until i disbear it worked by adding space before = and before 2
like =COUNTIF($A:$A,A8) = 2
try it
Hello,
Im trying to remove the duplicates and duplicates has appear in the next column.
Example:
In column A name has the below Alex,Tom,Tej,Alex,Tom,Alex.
Duplicates has to appear in the B column.
please help me on this
Hi, This is really helpful, would like to check, what if the fields that I wanted to control are already an data validation(drop down) field?
Please helped me a lot. I have a combination of data in 2 different columns which must be unique.
Example of correct data
emp id | Date
101 | 27/05/2016
101 | 28/05/2016
Example of Incorrect data
emp id | Date
101 | 27/05/2016
101 | 27/05/2016
So I want to stop duplicate data in a combination of columns. I am trying to do but fail always. Please help me in this regard i really need for this ....
Hi, Thank you very much for the information above. It is very helpful. I have a specific question for removing the near match duplicates.
eg. if particular column there are following entries and I want to keep only those with larger data point in the length.
RSAsVAGIVGADEEAPPAPK
SAsVAGIVGADEEAPPA
SsVDDGNINLTDTSTSNK
SsVDDGnINLTDTST
sASAAAILEEDDSKDDmEFK
TFsGIGFNLTEK
In this case following entries
RSAsVAGIVGADEEAPPAPK
SAsVAGIVGADEEAPPA
are the same but I need to keep RSAsVAGIVGADEEAPPAPK but not SAsVAGIVGADEEAPPA.
How it can be done for complete column with various entries? same is true with
SsVDDGNINLTDTSTSNK
SsVDDGnINLTDTST
I need to preserve the longer one and remove the shorter.
Please help me with this.
hi, this is really helpful
Hi,
When i applied Data Validation then it shows some error which says "Name range that you specified can not be found". Can you please help me on this error pls
I tried to do this so many times, i even installed the trim add-on, but it did not work. is there any other way?
I am happy after to learn excel command, how to avoid duplicate.
I am thanks.
NOT WORKING
Hello, Thank you for the tutorial! I read the part where you mentioned you'd be posting another tutorial on how to check duplicate entries within the same worksheet & even another workbook. I was hoping that you had posted the tutorial & could direct me on where to find it.
Thanks again!
Hello Sbeam,
Here's a full list of our tutorials that cover different ways to find and remove duplicates in Excel:
https://www.ablebits.com/office-addins-blog/tag/excel-duplicates/
Hopefully you will find a solution you are looking for in one of the articles or videos.
COPY AND PASTE FIX
I thought this was going to be perfect for my needs, until I realised this method doesn't work if copy and pasting data.
Ok, so this isn't a fix, but rather a solution that suited me and may suit others as well.
I followed the guide at https://www.ablebits.com/office-addins-blog/2013/09/13/auto-highlight-duplicates-excel/
if the link doesn't show or whatever, it's the article titled: How to automatically highlight duplicates in Excel once something has been typed.
How to ignore the first character when avoiding duplicates.
Example:
A70000011
B70000011
Consider this as a duplicate.
Also, not consider text a duplicate.
Example:
A--END--
B--END--
This would NOT be considered a duplicate.
Any ideas?
Is there a custome formula that would work in data validation for this?
I've wasted hours so far... Please help!
HI ,
Could you help me for combine multiple excel ( Car booking data) in one sheet.
Thanks
MUCH EASIER WAY:
CLICK FILTER ADVANCED BUTTON
SELECT COLUMN YOU WISH TO APPLY FILTER TO
CHECK THE BOX LABELED "UNIQUE RECORDS ONLY"
YOU ARE DONE, BOW THE DUPLICATES ARE ELIMINATED AND YOU CAN EMAIL YOUR PEOPLE
Am trying to prevent duplicates in a column of cells in our project log sheets. Each entry contains eight characters, with the first five being able to repeat, but the last three must be unique and sequential (i.e. JR157001, KR257002, ST457003, etc). We continue to get the last three duplicated with the first five being different and it throws everything into chaos when 10-15 entries later you duplicate an existing entry, then all outside files (other software) with the incorrect numbers have to be reworked, wasting a lot of time. I have tried COUNTIF and a few others, but there is always something no right with the formulas. Thanks.
Hi, how do I avoid duplicate entries on multiple sheets. E.g I have listed invoices by month to month on a different worksheet tab (One excel spreadsheet but each tab has say Jan, Feb). I've applied the rule and it works but I want it to pick up too if there was any duplicate entry from previous months (e.g I'm entering data for Feb and I'd like to pick up if there was any same invoice number already entered in previous months or tabs). Thanks
I have the same issue - did you find a solution?
I have bulk entries in column $b1:$b450 , it also contains repeated text like "AAA" in cells B2, B5, B8, B10 so on.
Now i want to give Sno in $A1:$A450, but i dont want that Serial number consider the duplicate text contain in other cells, it should treat 1 for all "AAA"
Let me know the formula
Thanks
how to validate name field in excel(cell should not accept number or special characters )?
Hey Josh,
please check this article out: how to apply data validation to cells
thanks a lot for the explanation but i am a trainer and want to prepare a sheet that highlights the names of the trainees who got the training in past years, so that to avoid duplication in trainings
Hello,
It would seem that for this to work, you need to have a range with cells following one another. In my instance, my cells are individual, i.e. there could be 3 rows seperating them. This means that when i select the data for which i want the rule, it comes up as individual cells rather than a range.
Any help is appreciated.
Can this work across tabs? I am trying to prevent duplicate entries being used across 3 separate tabs, all saved in the single file. The cells are the same in each tab (ie. the same column) in each tab if this helps?
Hi,
i have a list of data but all are 19 digits.
Because of this, duplicates are not identified.
Could you please share with us the solution.
Thanks
Anas
i want to extract the report where mobile number and DATE are same so dont consider duplicate. only consider duplicate mobile numbers which have different date.
Date Mobile
9/18/17 4:09 PM 923005254870
9/18/17 6:45 PM 923005254870
9/14/17 6:10 PM 923008435348
9/14/17 6:29 PM 923008435348
9/7/17 8:13 PM 923023689646
9/14/17 6:55 PM 923023689646
9/9/17 1:00 PM 923032819439
9/12/17 2:28 PM 923032819439
Hi There,
Minor issue detected.
For Word/Office 2013 the formula as mentioned in the article is not allowed.
The seperator between the range and the criteria must be a semicolon, instead of a comma, like this :
=COUNTIF($D:$D;D2)=1
Making this minor correction made it finally work for me ;-)
Thanks
CORRECTION
Hi There,
Minor issue detected.
For Excel 2013 the formula as mentioned in the article is not allowed.
The seperator between the range and the criteria must be a semicolon, instead of a comma, like this :
=COUNTIF($D:$D;D2)=1
Making this minor correction made it finally work for me ;-)
Thanks
This worked for me, thanks! One question: When the alert shows up, is there a way to track which cell the duplicate is in? Sometimes we have so many entries that manually looking for it can take a while. Thank you!
Hi,
It is not applicable when pasting. why that is so?
thx
THANK YOU, IT IS GREAT.
YOUR FORMULA SO GOOD AND WORKING, GET CONNECT WITH YOU
THANK YOU SO MUCH
Thank you alexander
Strange, everything seems fine. but no dialog box pop u if duplicate value entered.
i have excel sheet with contact number this should be duplicate which was repeating again and again
If I am copying the same data from another sheet to this column which I have validated,then this is not working out and does not throw any error.please help me in this
Hello Himanshi!
Please describe your problem in more detail.
How were you checking the column?
What data are you trying to copy and paste?
Does the size of the copy area coincide with the size of the paste area?
How is it connected with looking for duplicates?
If you give more information, I will try to help you.
The column I wish to eliminate the entry of duplicates is populated by alphanumeric characters. The formula =COUNTIF($A$1:$A$20,A1)=1 does NOT work:
- direct data entry (typing) meets automatic error message
- copy date entry is permitted even if the same date already in the column.
Excel 2016 - So what IS the correct, working method to prohibit duplicate entries?
Hello Rottweiller!
Unfortunately, Data Validation does not always work (it is mentioned in the last paragraph of the article).
It works only when you put information directly into a cell and press Enter to record changes at the end. If you replace data in a cell by copying or pasting another cell, Data Validation does not see those changes and cannot ban them.
I recommend using Conditional Formatting to see such duplicates.
You can use this formula as a condition: =COUNTIF($A$1:$A$20,A1)>1.
Alternatively, simply use standard Highlight Cell Rules -> Duplicate Values
it's working well while typing but it doesn't work when I copy & paste the same value, shows no error, and paste the same value.
can you sort our, please.
thankks
Hi, i want to make a list of employees using data validation- list command. How I can restrict duplication in a single day.
Prevent duplicate is not working for copy paste data.
=COUNTIF($D:$D;D2)=1 should read as =COUNTIF($D:$D;D1)=1 for it to work properly
I need to avoid a room reservation in a day if already book for a specific time (9:00am to 10:00am) of Room#1. plz suggest
Hi!
What do you want to calculate exactly? Your question is not entirely clear, please specify. Give an example of the source data and the expected result.