Free Online Tutorials on Spreadsheets

Prevent Duplicate Entries in Excel

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.    

Copyright © 2016 - 2019 | All Rights Reserved