Block Duplicate Entries in Excel Table

Data validation is a great feature in Excel, and I often use it to create a drop down list in a cell. That helps prevent data entry errors, and limits what people can input.

You can use data validation rules in other ways too. For example, you can prevent duplicate entries in an range or cells, or in a table column.

In this example, employee data is being entered in a formatted table, and each employee must have a unique ID number. The COUNTIF function can check the cells for identical entries, and warn you, or stop you, if an ID number is already in use.

preventduplicatestable02

Use COUNTIF to Check a Range of Cells

To create this data validation rule, I named the data cells in the EmpID column as EmpIDs. This step is necessary, because data validation can't use the table column name directly.

The good news is that named range is based on a formatted Excel table, so it is dynamic -- it will automatically grow or shrink if the number of rows changes. In older versions of Excel, you can use a formula to create a dynamic range.

Then, I used that range name, EmpIDs, in a COUNTIF formula.

=COUNTIF(EmpIDs,A2)<=1

To prevent duplicates, the count must be 1 or zero, and if not, a warning will appear when you enter a duplicate number.

preventduplicatestable01

Add an Error Message

If you enter a duplicate number, the data validation rule will show a default message, telling you to try again, or cancel your entry. You can personalize that message, to help people figure out what they’ve done wrong.

In the screen shot below, the message explains that the entered number is already in use, so you can cancel that, and try a different number.

preventduplicatestable02

You can read the detailed instructions on my Contextures website – Prevent Duplicates in an Excel Table – and you can see the steps in the video below.

Video: Prevent Duplicate Entries

This video shows you the steps for creating a formatted table, naming one of the columns, and setting up the data validation.

______________

10 comments to Block Duplicate Entries in Excel Table

  • […] was reading Contextures post about duplicate entries. In it, she creates a named range that refers to a Table column. That named range will expand and […]

  • Hello Debra,

    I can´t get it to work. I get an error if I use this formula:
    =COUNTIF(EmpIDs,A2)<=1

    I had to use this formula:
    =COUNTIF(INDIRECT("Table1[EmpIDs"),A2)<=1

    How did you do it? I am an excel 2010 user.

    It is also possible to prevent duplicate records:
    =COUNTIFS(INDIRECT("Table1[Name]"),$A5,INDIRECT("Table1[Fname]"),$B5,INDIRECT("Table1[Lname]"),$B5,INDIRECT("Table1[HireDate]"),$B5)<=1

    Thanks for posting!

    • @Oscar, I created a named range -- EmpIDs -- based on the data in the EmpID column.

      You can use that name in the formula, without the INDIRECT function.

      To use the column name, instead of a named range, you can use INDIRECT, as you discovered.

  • Samuel Kingston

    It is useful in entering unique records.

  • Elmer

    Hi Debra,

    Related to this question somewhat. I used some links on your site from an excel forum to help with my validation and create a list that hides already chosen entries. I am using the list from here: http://www.contextures.com/xlDataVal03.html.

    I've come across a problem and I thought you might have some insight. I need to take this and make it work with multiples of the same data. IE: The data is the numbers 4, 3, 3, 2, 2, 2, 1, 1, 1, 1. Right now, using the info from the link above, if I choose any of the multiples from the drop down it removes all of them, and I need it to only remove one for each time it's chosen.

    Any help would be extremely appreciated (and it might be a useful blog tip/post for other uses too!) Thanks in advance.

  • […] Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share it with […]

  • Martha Campodonico

    Debra,
    Love your formula, however I have a dilemma. I have a workbook of 7 sheets. I want to apply the same formula to all columns in all 7 sheets, and apply the same rule. How can I group all sheets, to make this work across all sheets? Thank you Deborah.

  • jothi

    i want prevent the double entry in with concatenate formula

  • Dan

    Debra,

    Love your examples. Great job!

    I'm not great with VBA, but might need to use in this case but thought I would check with you first --

    On a survey form, I have five different sections (lets call them Category 1, Category 2, Category 3, Category 4, and Category 5).

    Each category has 4 data fields that each requires a ranking from 1-4. Each category cannot have a duplicate number appear, you must enter only once a 1, 2, 3, and 4. But, all five of the sections (or categories) will all have the numbers 1, 2, 3, and 4 entered.

    I need for each category its own data validation formula that ensures that each number 1, 2, 3, and 4 have only been entered/used once and that each category does not contain a duplicate number entered.

    From what I have read, I would use a "list" allow in the data validation to provide a drop down list. But I need a "custom" allow to prevent duplicates. How can I have both?

    I hope that is clear. If not, please let me know.

  • RIAZ QADEER

    THIS WILL BE MUCH HELP FULL FOR ME, THANKS VERY MUCH

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>