info@axonoid.com

Prevent duplicate data entry in Google Sheets

Prevent duplicate data entry in Google Sheets

Prevent duplicate data entry in Google Sheets

One of the most common data entry mistakes is entering the same value multiple times. Duplicate data entry could be prevented by taking advantage of Google Sheet’s built-in data validation feature.

Begin with selecting the column or range that contains your data.

Right click on your selection and choose Data validation.

Change the Criteria to Custom formula is.

Enter the following custom validation formula. Assuming your data is in column A, it compares each cell against the entire column and counts how many times it appears, minus one (excluding the cell itself). It triggers the data validation error if there is at least one duplicated value.

=IF(COUNTIF($A$2:$A$1000,INDIRECT("A"&ROW()))-1 <= 0,TRUE,FALSE)

The data validation feature could either just show a warning on the duplicated cell or prevent adding values that already exist. The Appearance option allows for displaying a custom message when invalid data is entered.