Main Lesson: Data Validation in Excel
In this example, we will illustrate how to prevent duplicate entries using the data validation feature.
1- Select range of cells (A4:A23).
2- Press Alt + A + V or alternatively, go to the Data tab, in the Data Tools section, select Data Validation.
3- The Data Validation box will appear, from the Allow option select Custom and enter the following formula: =COUNTIF($A$4:$A$23,A4)=1. Press OK.
4- Result. Excel will prevent duplicate entries.
Explanation. The formula used (=COUNTIF($A$4:$A$23,A4)=1) contains the absolute reference and counts the number of values in the range A4:A23 that are equal to the value in cell A4. Due to data validation, Excel will automatically copy the formula to the other cells.