Tom’s Tutorials for Excel: Prevent Duplicate Entries

Tom’s Tutorials for Excel: Prevent duplicate entries

There are times you will be using Excel that duplicate entries could be disastrous such as lists of employee’s Social Security numbers, patient’s confidential medical records or student’s contact information information to name a few. Here Tom Urtis explains how to ensure that never happens:

If you are entering a list of values that are meant to be unique, such as employee Social Security Numbers, you would want to ensure that duplicate entries are not allowed. Data Validation can be applied to the cells, for example range A2:A20, so only unique entries are allowed.

Select range A2:A20 and then…

In Excel version 2003 or before:
From the worksheet menu, click Data > Validation.

In Excel version 2007 or 2010:
From the Ribbon, click the Data tab, and in the Data Tools section, select Data Validation.

From the drop down list select Data Validation.

Alternatively, from any version of Excel you can show the Data Validation dialog box by pressing Alt+D+L on your keyboard.

In the Data Validation dialog box, select the Settings tab. From the Allow drop down list, select Custom.
In the Formula field, enter
=COUNTIF($A$2:$A$20,A2)=1

Note the relative and absolute references in the Custom formula.

Click onto the Input Message tab to enter optional text, such as entry instructions, that will appear when the user selects that cell.

Click onto the Error Alert tab and select “Show error alert after invalid data is entered”. From the Style drop down list, select Stop, and in the Title and Error message fields, enter the text you want your users to see in the warning message that will appear when a duplicate entry is attempted.

Click OK to confirm your Data Validation settings.