Excel prevent duplicate values

In cases where it is necessary for a user to enter unique values ​​in Excel and therefore no duplicate values ​​may occur, this can be enforced using data validation. Suppose column A contains the following data:

In this case it is preset that no duplicate values ​​can be entered in A2:A100. This is done as follows. Make sure the input column is empty. Then from A2 select cells A2:A100 and choose the option Data Validation (this option can be found on the ribbon at the Data tab, but this can differ per Excel version). Then in the Data Validation screen, under the Settings tab, select at 'Allow:' the option Custom:

At 'Formula:' the following formula must then be entered:

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

In this case, the input is checked for duplicate values ​​for A2 through A100. This can of course be adjusted. Optionally, a specific error text can be entered on the Error message tab that will appear when the user enters a double value. Then choose OK and the check for entering duplicate values ​​is set for the cell range A2:A100.

If, for example, in the example above in cell A11 the value 5 is entered, then that is not possible and the following error message appears:

This message also appears if an attempt is made to change an already entered value to one that already exists.

This solution works fine in most cases, but data validation does have some limitations. For example with 'copy - paste' or Ctrl+D you could still enter duplicate values ​​without an error message. With VBA you could optimize this even further by turning off Ctrl + D for the workbook and preventing copying and pasting for that cell range. But then it is better to choose a full VBA approach.

First give cell range A2:A100 the name 'no_duplicates' and then put the code below in the Worksheet_Change event of the relevant worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, bUndo As Boolean, cl As Range, bDuplicate As Boolean
    If Not Intersect(Target, Range("no_duplicates")) Is Nothing Then
        Set rng = Intersect(Target, Range("no_duplicates"))
        bUndo = False
        If rng.Cells.Count > 1 Then
            For Each cl In rng.Cells
                If Application.CountIf(Range("no_duplicates"), cl.Value) > 1 Then
                    bDuplicate = True
                End If
            Next
            If bDuplicate Then
                MsgBox "One or more of the entered values already exists and duplicate values are not allowed!", vbCritical, "Duplicate value"
                bUndo = True
            End If
        ElseIf Application.CountIf(Range("no_duplicates"), rng.Value) > 1 Then
            MsgBox "The entered value already exists and duplicate values are not allowed!", vbCritical, "Duplicate value"
            bUndo = True
        End If
        If bUndo Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If
End Sub

This piece of code also checks for duplicate values if a user copies multiple values at once.

Questions / suggestions

Hopefully this article helped you to prevent entering duplicate values. If you have any questions about this topic or suggestions for improvement, please post a comment below.