How to Use Data Validation to Prevent Duplicate Entries in Google Sheets

The Threat of Duplicate Data

When multiple users are collaborating on a single Google Sheet, data integrity often suffers. If you are using a spreadsheet to track invoice numbers, employee ID codes, or customer email addresses, entering the same data twice can cause massive issues downstream in your reporting or billing software.

While you can use conditional formatting to highlight duplicates in red after they happen, it is much more effective to prevent the duplicate from being entered in the first place. By combining Google Sheets’ Data Validation feature with a custom COUNTIF formula, you can physically block users from typing a value that already exists in the column.

Step 1: Select the Target Range

First, identify the column where uniqueness is absolutely required. For this example, let us assume Column A is designated for unique “Invoice Numbers”, and the data entry begins in row 2.

1. Click on cell A2.
2. Hold down the Shift key and press the Down Arrow to highlight the rest of the column (e.g., A2:A1000).

Step 2: Open Data Validation

With the cells highlighted, you need to apply a rule to them.

1. In the top menu bar, click on Data.
2. Select Data validation from the drop-down menu.
3. A sidebar will appear on the right side of the screen. Click the + Add rule button.

Step 3: Write the Custom Formula

By default, the criteria will likely be set to “Drop-down”. You need to change this to a custom mathematical rule.

1. Click the Criteria drop-down menu.
2. Scroll to the very bottom and select Custom formula is.
3. In the formula box that appears, enter the following exact formula:
   =COUNTIF(A$2:A$1000, A2)=1

How this formula works:

The COUNTIF function looks at the entire range (A2 through A1000) and counts how many times the value in the current cell (A2) appears in that range. The =1 at the end is the logical test. It tells Google Sheets: “Only allow this data entry if the final count is exactly 1.” If someone types a duplicate, the count becomes 2, the rule evaluates to FALSE, and the entry is flagged.

Note: The dollar signs ($) are critical. They lock the lookup range so that as the rule applies down the column, it always checks the entire column, not just the rows below it.

Step 4: Block the Input

By default, if a data validation rule is broken, Google Sheets just puts a tiny red warning triangle in the corner of the cell but still allows the bad data to exist. We want to stop the user completely.

1. In the Data Validation sidebar, scroll down to the Advanced options section and click it to expand.
2. Under “If the data is invalid”, select the radio button for Reject the input.
3. Check the box for Show validation help text.
4. In the text box below it, type a custom error message, such as: “Error: This Invoice Number has already been entered. Please check your records.”
5. Click Done.

Now, if a user types “INV-100” into cell A2, and then accidentally tries to type “INV-100” into cell A50, Google Sheets will instantly throw a hard error pop-up displaying your custom message, and the duplicate text will be erased.