Google Sheets Data Validation for Duplicate Entries

Yes. Google Sheets can reject duplicate entries with a custom-formula data validation rule: for a list in A2:A, apply the rule to A2:A and use =OR(A2="",COUNTIF($A$2:$A,A2)=1), then choose Reject input. The formula allows blank cells but stops a repeated nonblank value from being entered through ordinary cell editing.

What duplicate validation does in Sheets

Google Sheets does not have a separate data-validation option labeled “unique values.” Instead, a custom formula checks whether an entry meets a condition, and the rule accepts it only when the formula returns TRUE. COUNTIF counts how often a value occurs in a range; comparing that count with 1 makes a value invalid if it appears more than once.

This is useful for lists that should not contain the same email address, order number, employee ID, product code, or other identifier twice. It is different from highlighting duplicates: validation can prevent an entry from being accepted, while conditional formatting only changes how a cell looks. Neither one automatically finds and removes old duplicates.

Set up a rule to reject duplicate entries

Suppose column A contains email addresses, with a heading in A1 and entries beginning in A2. In Google Sheets in a web browser:

Select the cells to validate, such as A2:A1000, or select A2:A if the list may grow.
Choose Data > Data validation. The Data validation rules panel opens.
Choose Add rule if needed, and check that Apply to range covers the intended cells.
Under Criteria, choose Custom formula is.
With the list beginning in A2, enter =OR(A2="",COUNTIF($A$2:$A,A2)=1).
Expand Advanced options if necessary. Under If the data is invalid, choose Reject input, then select Done.

The formula assumes the first validated cell is A2. The relative reference A2 adjusts for each row; the dollar signs keep the counted range anchored at column A, row 2. If the selection begins in A5 instead, change the formula’s relative cell reference to A5 and start the counted range at $A$5:$A. A mismatch between the first applied cell and the formula reference is a common reason a rule appears to behave inconsistently.

Allow blanks and choose the right range

The OR at the start of the example matters. A blank cell is usually not a duplicate that should be rejected. Without the blank check, COUNTIF can count other empty cells in the range, making a rule designed for unique values reject blank entries unexpectedly. The A2="" test permits an empty cell; the COUNTIF test applies to nonblank values.

Choose a range that fits how the sheet will be maintained. A2:A1000 is explicit and avoids applying the rule to unused rows beyond that point. A2:A is convenient for a growing list and includes future rows. In either case, keep the header outside the validated range. When you add a rule to an existing range, inspect the Apply to range field rather than assuming Sheets selected the right cells.

Formulas in this article use commas as argument separators. Depending on the spreadsheet’s locale, Sheets may require semicolons instead. If a formula is rejected, check the locale under File > Settings and use the separator expected by that sheet.

Find duplicates that are already in the list

Data validation is not a cleanup tool: applying a rule does not erase or fix values already present. To identify existing repeats, use conditional formatting. Select A2:A, choose Format > Conditional formatting, and set the formatting rule to Custom formula is. With values beginning in A2, use =AND(A2<>"",COUNTIF($A$2:$A,A2)>1), then choose a highlight style and select Done.

This highlights each nonblank value that appears more than once, making it easier to decide which row to keep or correct. It does not delete duplicates or change the data. Another option is to sort the list or use a separate review column, but preserve a copy before doing a large cleanup so you can restore information if needed.

Decide what counts as a duplicate

The sample rule treats entries as duplicates according to COUNTIF’s matching behavior. In particular, text matching is not case-sensitive: Alex and alex count as the same text for this purpose. Leading or trailing spaces can make two entries look alike while being stored differently, so an address such as name@example.com followed by a version with a trailing space may not behave as a clean, identical match.

For consistent results, standardize input before relying on uniqueness. For example, remove accidental spaces and agree on how email addresses, names, and codes should be entered. Do not assume that two labels that look similar are equivalent: identifiers with punctuation, spaces, or different spellings may be distinct values. If case or formatting is meaningful for your identifier, test the rule with representative examples before applying it to a working register.

There is also a difference between preventing a repeated value and preventing a repeated record. If a person may appear more than once but should not have the same person-and-date combination repeated, checking only the name column is too strict. Define the duplicate key based on the fields that make a record unique.

Check duplicates across two columns

For records that are unique only as a pair—for example, a product code in column A combined with a warehouse in column B—use COUNTIFS to count rows matching both fields. With headers in row 1 and the first record in row 2, apply the rule to the relevant entry range and use =COUNTIFS($A$2:$A,A2,$B$2:$B,B2)=1. This accepts a pair only when that combination occurs once.

Plan how incomplete records should behave before using a pair-based rule. The formula above checks both values, but it does not impose a general requirement that both fields be filled in. If each record must contain both a product and a warehouse, add separate required-field checks or use a rule designed for that workflow. Testing a complete record, a partial record, and a repeated pair helps reveal whether the rule matches the intended policy.

Limits of duplicate validation

Reject input is a useful safeguard for direct edits, not a guarantee that every possible way of changing a spreadsheet will preserve uniqueness. Copying and pasting, importing data, or changing the validation rule can introduce invalid values or alter the rules applied to cells. For that reason, check the list again after a bulk paste or import, especially before using it for a shared process.

Validation also does not resolve simultaneous edits as a database would. In a shared sheet, two people may be working on different cells at the same time, and a simple cell-level rule should not be treated as an audit system or a security boundary. For important records, restrict editing access appropriately and make a routine duplicate check part of the review process.

One more practical limitation: a cell’s validation rule has a selected criterion, such as a dropdown or a custom formula. A custom-formula rule is not the same as a dropdown rule, and the standard rule interface does not combine the two criteria into one rule for the same cell. If you need a dropdown and uniqueness enforcement together, consider a separate validation/checking design or an Apps Script workflow, and verify that the added complexity is worth it.

Choose between validation and other options

Use custom-formula validation when the goal is to stop ordinary duplicate entry as people fill in a list. Use conditional formatting when the goal is simply to spot duplicates without blocking edits. For one-time cleanup, the Remove duplicates tool under Data can remove repeated rows or values, but review the selected range and keep a backup first because removing records can discard information.

For a recurring import or a workflow with more complex rules, a separate review column, a controlled form, or Apps Script may be more suitable. Those approaches can handle requirements that a simple COUNTIF rule cannot, but they also need testing and ongoing maintenance. In Excel, the equivalent idea is a custom formula in Data Validation, but Excel presents its controls in a validation dialog rather than Google Sheets’ side panel; instructions and paste behavior can differ between programs.

Frequently Asked Questions

Can Google Sheets automatically remove duplicate entries?
No. Data validation can reject certain new entries, but it does not remove existing repeats. Use conditional formatting to find them or Data > Data cleanup > Remove duplicates for a cleanup task, reviewing the selected range before removal.

Why does my duplicate rule reject blank cells?
A COUNTIF check alone may count blank cells in its range. Add a blank exception, as in the OR formula above, and ensure the formula’s first cell reference matches the top-left cell in Apply to range.

Does the rule treat uppercase and lowercase as different?
Not with the COUNTIF example: text matching is not case-sensitive, so Alex and alex are treated as duplicates. If case distinctions matter, use a case-sensitive approach and test it carefully with sample data.

Can I use the rule with a dropdown list?
A custom-formula rule and a dropdown criterion serve different purposes and are not combined as two criteria in the standard rule for one cell. A separate checking column or a more advanced workflow may be needed if you require both dropdown choices and duplicate prevention.

Will the rule catch duplicates pasted into the sheet?
Do not rely on it as a complete safeguard for bulk pastes or imports. Those operations can introduce invalid values or affect rules, so run a duplicate check after bringing in data and confirm that the validation still applies to the full intended range.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.