Automatically Stop Duplicate Values on Input

Preventing duplicate values from being entered in a range of cells may be a requirement in some of your worksheets. Although not obvious, enabling this capability in Excel is very easy.

You can prevent duplicate values from being entered in a range of cells through Data Validation.

1) Select the range of cells where you want to prevent duplicate values (in this example, B3:B20).

2) In Excel, on the Data tab, click Data Validation.

3) Once in the Data Validation dialog box, under the Settings tab click the Allow dropdown and select Custom from the list. A Formula field will appear.

4) In the Formula field type =COUNTIF($B$3:$B$20,B3)<2. When data is entered into a cell, this formula is evaluated. It looks for duplicate values. Absolute values are required here because you want to compare the top of the list to the data that appears below it. It is essential that you use Absolute referencing ($B$3 not B3) for your validation range (e.g. $B$3:$B$20) and relative referencing for your Active Cell (e.g. B3).

5) In the Data Validation dialog, click the Input Message tab and, if desired, type a message that will be displayed when the user selects a cell within the range of the Data Validation rule. Otherwise, if you don’t want a message to appear every time someone selects a cell in the range, clear the ‘Show input message…’ option.

6) Click the Error Alert tab and type the message that will be displayed if the user enters a duplicate value in the range.

7) Click OK.

Now test your Data Validation rule by entering values, including some duplicates, within the range.