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 365, 2021, 2019, 2016, 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. Table withous duplicates. All email addresses must be unique

  1. 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.
  2. 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. Select the entire column where you need to avoid duplicates

    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 Select all the cells in your column

  3. Go to Excel "Data" tab and click on the Data Validation icon to open the dialog box. Go to Excel 'Data' tab and click on the Data Validation icon to open the dialog box
  4. On the Settings tab, choose "Custom" from the Allow drop down list and enter =COUNTIF($D:$D,D2)=1 into the Formula box. Formula to count duplicate entries in a column

    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.

  5. 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." Only unique emails are allowed.

  6. 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: Duplicate entry error

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. Data validation: Warning style

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. Information about new duplicate entry

Note: I'd like to pay your attention again to the fact that the alert about a duplicate entry will appear only when you try to enter a value into a cell. Excel will not find existing duplicates when you configure the Data Validation tool. It will not happen even if there are more than 150 dupes in your column. :).

Table of contents

136 comments

  1. list of top packers and movers in delhi

  2. nice toturial.

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

  4. I am inputting just a straight number i.e. 1150600542
    as an example number so nothing irregular.

  5. 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?

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

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

  9. 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?

  10. yes correct but still remain same problem using with copy-paste,
    how to useful with copy-paste?

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

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

  13. 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 ?

  14. 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!

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

  16. 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?

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

      • 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).

  18. alex, I emailed you.

    Thanks,
    Niall Williams

  19. 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 Trim Spaces tool.

      • Hi,
        Thank so much.
        It`s same issue happen with me, i could not use this formula.
        Could you please assist.

        Regards

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)