Prevent Duplicates in Excel

A common problem when using Excel is handling duplicate entries. I am often asked about removing or highlighting duplicates and common entries.

But prevention is better than the cure, so let’s look at how to prevent duplicates in Excel.

We can use the Data Validation tool with a formula to stop duplicates from being entered by the user. In this example we want to prevent duplicates from being entered into column A.

1. Select the whole of column A by clicking the column header
2. Click the Data tab on the Ribbon and click the Data Validation button
3. Click the Allow: list arrow on the Settings tab and select Custom

4. Enter the following formula in the Formula box

=COUNTIF($A$1:$A1,$A1)=1

The COUNTIF function is used to count how many times the value just entered appears within column A.

Cell A1 is fixed to signify the start of the range, and the end of the range will automatically expand as more values are entered.

The Data Validation tool checks to see if the value has occurred only once. If it hasn’t then the entry will be prevented, and an error alert will be displayed to the user.

5. Click the Error Alert tab. Type “Duplicate Entry” in the Title: box and “You have entered a duplicate entry.” in the Error message: box

6. Click Ok

Data Validation will prevent duplicates in Excel. If a duplicate value is entered the following error message will be displayed.