Prevent Duplicate Records in Excel

This example will show you how to use data validation to prevent users from entering the same values.

1. Select a range A2: A20.
2. On the Advanced tab Data (data) click Data Validation (Data validation).
3. Выберите Custom (Other) from the drop down list Allow (data type).
4. Enter the formula shown below in the field Formula (Formula) and press OK.

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

   =СЧЕТЕСЛИ($A$2:$A$20;A2)=1

   Explanation: Function COUNTIF (COUNTIF) takes two arguments and counts the number of values ​​in the range A2: A20, equal to the value in the cell A2. Since we do not need duplicate data, such a value can occur only once (=1). Before opening the data validation window, we selected the range A2: A20, so Excel automatically copied the formula to the rest of the cells in that range. Note that we have created an absolute reference to the range.

5. Highlight a cell A3 And click the Data Validation (Data validation).As you can see in the figure, this function counts the number of values ​​in the range A2: A20, equal to the value in the cell A3. Again, this value can only be found once, as we want to avoid duplication of data.
6. Enter a duplicate value.

Result: Excel generates an error message because you have already entered this number.

Note: To specify an input hint or text to alert you to an error, click the tab Input Message (Message for input) or Error Alert (Error message).