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.

______________

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

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=""> <strike> <strong>