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

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

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

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

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

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

  7. I would like to stop duplicate value entry in a self generating list. Any idea how to do it.

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

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

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

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

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

  13. thnx, it war really helpfull!

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

  15. It did not work at first but after I unchecked the "ignore blank" box next to custom it worked. Very helpful. Thank you.

  16. It is not working in my excel sheet. please Help me..

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

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

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

  20. thanks for your SPAM !

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