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

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

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

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

  5. how to validate name field in excel(cell should not accept number or special characters )?

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

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

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

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

  10. HI ,

    Could you help me for combine multiple excel ( Car booking data) in one sheet.

    Thanks

  11. Is there a custome formula that would work in data validation for this?

    I've wasted hours so far... Please help!

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

  13. 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/how-to-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.

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

  15. NOT WORKING

  16. I am happy after to learn excel command, how to avoid duplicate.
    I am thanks.

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

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

  19. hi, this is really helpful

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

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 :)