Skip to content

Prevent Duplicate Values in Google Sheets

In this article, I will show you how you can use data validation to prevent duplicate values from being entered into a specific range within your Google Sheets spreadsheet.

The Example

To demonstrate this concept, I will be using the following example.

Here I have some products, and my goal is to ensure that all of the Product Id numbers are unique values.

In other words, I want to prevent the entry of duplicate Product Id numbers.

Add Data Validation

The first step to prevent duplicate values is to select the range where you want to prevent duplicate entries.

With this range selected, go to Data Data Validation.

In the panel on the right, change the “Criteria” to custom formula is.

Then in the formula bar, type in the following formula.

=COUNTIF(dataValidatedRange, topCellOfRange)<=1

The data-validated range should be an absolute reference, and the top cell of the range should be a relative reference.

In my example, the exact formula would be:

=COUNTIF($B$3:$B$8, B3)<=1

After you’ve entered the formula, click on advanced options.

Under “if the data is invalid,” select the reject the input option.

Then if you want to customize the message shown to the user when they enter a duplicate, check the box next to “show help text for a selected cell.” You can then type in the message you want to be seen.

After all that, you can click on Done.

The Formula Explained

Let’s take a closer look at the formula.

=COUNTIF(dataValidatedRange, topCellOfRange)<=1

For each cell in the data-validated range, the formula will calculate the number of times the value in that cell appears.

If the user enters a duplicate value, that will cause the formula to evaluate to FALSE. As a result, the data validation will stop that value from being entered.

Download the Example File

If you are still confused, I encourage you to download the example file using the link below.

Take a look a the Formula Explained page, and tinker with the data validation added to the Example page. Sometimes, the best way to learn is by working with things hands-on.

Google Sheets Example File

This Post Has 2 Comments

  1. I never thought of using data validation for this! I can think of so many things this would be helpful for! Is this also available in Excel?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top