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. I am receiving an error message on new entries where there are no duplicates.
    countif($C:$C,c3)=1

    1. Hi! Unfortunately, this information is not enough to give you any advice. What data are you using? What type of error do you get?

  2. Hi, am trying to avoid entering numbers that have already been entered in a different excel worksheet. what formula can i use?

      1. Alright. Thank you

  3. Manually entering duplicate values in this case seems to work, such as entering "123". When I input the data manually, it works fine. However, when I try to copy and paste the data, the cell accepts it, indicating that the data validation is not functioning properly in this scenario.

    1. Hi! Unfortunately, validating data in Excel only works when typing. In your case, you need to use a VBA macro to check values when a cell changes.

      1. Thanks Sir!

  4. Hi,

    I am going to make big Excel table and want to avoid duplicate addresses in it (Excel 2016).
    I have tried a solution with Data Validation and a formula posted here ( =COUNTIF($D:$D,D2)=1 ) but Excel does not accept it as a good formula.
    What should be done ?

    Regards,

    Kresimir

    1. Hi! I can't see what you're doing and tell you what you've done wrong. Follow the instructions in the article above carefully.

  5. Hi.

    I have several sheets in which I've used list data validation but it doesn't help stop duplicate entries... I need a particular data fetched from a previously checked list to avoid punching mistakes but it costs me duplicate entries. How can I stop duplicates while using allow list option in data validation, please help.

    1. Hi! If I understand the question correctly, you are using a drop-down list to enter data. The drop-down list writes text values to the cell. To prevent duplicates, you can use the methods suggested in the article above. If this is not what you wanted, please explain the problem in more detail.

  6. Hey,

    I have a button which has a VBA Macro attached that imports data from a different sheet, and it seems like this bypasses the data validation. Do you have any tips on how to correct this?

    Thanks for the help.

  7. Eres lo máximo, millones de gracias!

  8. The instructions were clear, but doesn't seem to work for me...

    My range of names that I do not want duplicated are on sheet1, whereas the input cell is on sheet 2. (Feeds into another macro for naming the tabs) when I try to complete using the formula, the input cell will still accept any value. I've tried the formula on the input cell and the range but still cannot get it to work.

    Any advice please?

  9. First of all thanks for this great services you offer many end-users out there.

    Secondly, I have a question in relation to a searchable database in Excel where a Pivot Table is used, and a "Remove Duplicate" is ticked. Now assume that a business wants to keep records of the usage a vehicle whose registration number is N111-222W which was used by different staff members from 01 July 2023 to 30 July 2023. In the vehicle registration number the number N111-222W has to appear repeatedly by default, how can I record the usage history without duplicating the registration number?

    Regards,

    Reha

  10. Hi,
    Great tutorial! This is exactly what I needed.

    In my Sheet, F3:F202 is the range where the user would select an employee's name from a pulldown list. Each cell in the range already has a Data Validation Allow: List associated to it with Source Field: =EmpLst, a named range of employees names on another Sheet.

    How can I prevent a user from entering a duplicate name within the same range using =COUNTIF($F:$F,F3)=1 if I already have a Dropdown List?

    1. Hi! Unfortunately, you cannot use two Data Validation rules in the same cell. To prohibit entering duplicates in a range, you can use VBA.

  11. Just an add-on to the well-described steps over here.

    I wanted to apply this technique to an excel file, that was stored on a remote terminal server.
    For some reason, I would always encounter an error while entering the formula in Data Validation ("Are you trying to enter a formula?")

    I copied the file onto my local PC and tried doing the same, and there were no more errors.

    It turns out, the system settings for the separators were different for the remote terminal server. So instead of:

    =COUNTIF($D:$D,D2)=1

    I used:

    =COUNTIF($D:$D;D2)=1

    Note the semi-colon instead of the comma separator.

    It took me a day to get around this. If somebody is also encountering the same issue, I hope this helps!

    Thanks, Ablebits, for the wonderful help!

    1. This accepts the changes now, but it does not stop duplicates. What is the problem?

  12. i have 2columns, start dan end columns. i want to do
    1. end should larger than start
    2. customer need to enter start first then enter the end column.

    i try do below but not work well

    =AND(B2>A2,COUNTBLANK(A2)=0)

  13. Hi, I want to automatically move one column values to another column but only the unique values. for example: I have John 2 times in column A:A, now I want to automatically move John to column B:B but just as a unique value.
    I hope it does make scenes?

    1. Nataka andika kwenye column 1 let say E then once written ionekane kwenye column nyingine let say B

      1. Thank you so much!

  14. Hi,
    I am creating a time sheet for my team to use and I have three columns that I want to prevent people from entering the same data in the columns are time off/holiday leave, alternate work schedule day and comp time used. If somebody puts 8.25 in the alternate work schedule column I don’t want them to be able to put anything in the other two columns for that same day. And if somebody puts 8.25 in the time off column I don’t want anyone to be able to put that same value in either of the other columns for that same day. I hope that makes sense so far the formulas I’ve tried I haven’t worked. Is there a way to do this? Thank you so much

    1. Hello!
      If I understand the problem correctly use the data validation tool with these formulas

      =((A1<>B1)*(A1<>C1)*(B1<>C1))+(COUNTBLANK(A1:C1)>1)

      or

      =(COUNTIF(A1:C1,A1)+COUNTIF(A1:C1,B1)+COUNTIF(A1:C1,C1))<4

  15. Will this duplicate rule only work when we enter a duplicate value or will it work with copy-paste also? (pasting the field value from other sourse)

    1. Hello!
      When searching for duplicates, it is important what value is written in the cell. How it was written does not matter.

  16. Why values keep on duplicates even not match . refer below numbers
    511010002201816129
    511010002201816128

    1. Hi!
      The information you provided is not enough to understand your case and give you any advice, sorry.
      What formula do you use to identify duplicates?

      1. just mentioned on angelie issues, it's same as my issue. this formula not work when user input long number such as 18-20 digit.
        Can you advise how to deal with it. Actually this number is different 511010002201816129-511010002201816128 but excel not allow as it is duplicate.

        1. Hi!
          Excel allows no more than 15 digits in a number. It replaces the rest of the digits with zeros. So you get duplicate numbers. 511010002201816129 will be written as 511010002201816000.

          1. If we want above 30 characters what we have to do??

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

  18. =COUNTIF($D:$D;D2)=1 should read as =COUNTIF($D:$D;D1)=1 for it to work properly

  19. Prevent duplicate is not working for copy paste data.

  20. Hi, i want to make a list of employees using data validation- list command. How I can restrict duplication in a single day.

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