Excel Tips – How to Prevent Duplicates while entering data

Use the Data Validation + Countif function. It Prevents the user from entering Duplicate data.

To do this:

1. Select a range of cells, for example, A2:A20.

2. On the Ribbon click <Data> à <Data Validation>

3. Select the [Settings] tab.

4. From the Allow dropdown list, select Custom.

5. In the Formula box, enter the following formula:

=COUNTIF($A$2:$A$20,A2)=1

6. Type an appropriate error message in the [Error Alert] Tab