Prevent Duplicates in Google Sheets: 3 Reliable Methods

To prevent duplicate entries in Google Sheets, select the cells where new values will go, open Data > Data validation, and add a custom-formula rule that rejects a value already in the list. For names, email addresses, or IDs in column A starting at A2, a suitable rule is =OR(A2="",COUNTIF($A$2:$A$1000,A2)=1). This guide shows how to set up that check, find duplicates already present, and handle common exceptions in Google Sheets in a web browser.

What You’ll Need

A Google account with permission to edit the spreadsheet.
A clear idea of what counts as a duplicate: a repeated value in one column, or a repeated combination of values across several columns.
A column or range reserved for the entries you want to check. The examples use column A for email addresses or IDs, with a header in A1 and data starting in A2.

Before changing a shared or important sheet, consider making a copy with File > Make a copy. That gives you a safe version to compare if cleanup removes information you meant to keep.

Step-by-Step

Decide what should be unique. Pick the field that identifies a record. An email address, order number, or membership ID is usually a better key than a person’s name, since two people can share a name. If a record is only unique when two fields are considered together, such as a product code and store location, use the combined-field method in step 6. Also decide whether you intend to treat upper- and lowercase text as the same; the COUNTIF examples below are not case-sensitive.
Check the layout and existing values. Keep a header in row 1 and entries below it, with one record per row. Look for blank rows inside the data and make sure the values you are checking are all in the same column. If there are already duplicates, decide which record to keep before setting up prevention. A new rule can stop some new entries, but it does not automatically repair values already in the sheet.
Apply a validation rule to the entry range. Select A2:A1000, or choose a range large enough for the list you expect. Select Data > Data validation, then choose to add a rule if the side panel does not open with one ready to edit. Under the criteria options, select Custom formula is. With the selected range starting at A2, enter =OR(A2="",COUNTIF($A$2:$A$1000,A2)=1). The references to the full checking range are fixed with dollar signs; A2 is relative so the rule checks the value on each row. The OR part allows empty cells, while COUNTIF allows an entry only when it appears once in the range.
Reject invalid entries and save the rule. Expand the advanced options if necessary. Under the setting for invalid data, choose Reject input, not a warning-only option. You can add help text such as “Enter an email address that is not already listed” so editors know why a value was refused. Select Done to save. If your intended list will exceed row 1000, extend both the selected range and the formula’s checking range before relying on the rule.
Test both an allowed and a repeated value. Enter a new value in a blank cell in the validated range; it should be accepted. Then enter a value already present in column A; Google Sheets should reject it and show the validation message. Test a blank as well if blanks are allowed. If you see no rejection, check that the rule covers the row you tested, that the formula begins with an equals sign, and that the invalid-data setting is Reject input.
Highlight duplicates so you can spot them. Select A2:A1000, then choose Format > Conditional formatting. In the conditional-formatting panel, set the formatting rule to Custom formula is and enter =COUNTIF($A$2:$A$1000,A2)>1. Choose a noticeable fill color and select Done. This flags every value that occurs more than once, including existing duplicates. Highlighting is a visual check, not a block on entry, so keep the validation rule too.
Remove duplicates that are already in the data, if appropriate. Make a copy first if you are unsure which rows should remain. Select the full table, including all the columns that belong to each record, then choose Data > Data cleanup > Remove duplicates. Indicate whether the range has a header and choose the columns that define a duplicate. Review the result message and inspect the remaining rows. Selecting only the key column can remove values from that selection without preserving the corresponding records as intended; selecting the full table and choosing the key column for comparison is safer when you need complete rows retained. This cleanup is a one-time action, not a rule for future entries.
Protect rules when the sheet is shared. Tell editors which column is the unique key and avoid asking them to paste over the header or the validation range. After importing or pasting a batch of data, scan the highlighted range and confirm the data-validation rule is still present. Validation is useful for routine entry, but it is not a substitute for checking a large import.

For a duplicate defined by two columns: Suppose column A contains a product code and column B contains a location, with data in rows 2 through 1000. Select A2:B1000 and create a custom-formula validation rule using =OR($A2="",$B2="",COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2)=1). This allows a partially completed row, but rejects a product-and-location pair that already appears together. Adjust the column letters and range to fit your sheet. The rule checks the pair, so the same product can still appear at a different location.

Common Mistakes

Choosing the wrong key. Preventing repeated names can block legitimate people with the same name. Use an ID or email address when it is the field that should be unique.
Applying a formula to the wrong starting row. A custom formula is evaluated relative to the first cell in the selected range. If your range starts at A3, the row reference should start at A3, not A2.
Using a warning instead of rejection. A warning can alert an editor while still letting the duplicate through. Choose Reject input when the goal is to prevent normal direct entry.
Checking too small a range. If the rule checks only rows 2 to 1000 but the list grows to row 1001, entries beyond the checked range may not be caught. Expand the range as the list grows.
Confusing highlighting with prevention. Conditional formatting only changes how cells look. It does not stop anyone from entering a repeated value.
Removing records based on too many columns. The cleanup tool compares the columns you select. If you compare every column, two records with the same email but different notes may not count as duplicates. Choose the actual key column or columns, while selecting the full table when you need to keep complete records.

Tips

Prefer stable IDs. A unique ID is less likely to change than a name or address. If people type IDs manually, provide a consistent format and explain whether spaces or punctuation are allowed.
Normalize values before comparing when needed. Extra spaces can make values look alike to a person while differing as stored text. Consider using a clean input process or a separate cleaned helper column; do not add trimming or case conversion unless it matches the way your records should be identified.
Use a bounded range for a large sheet. The examples check through row 1000. A deliberate range can be easier to understand and maintain than an unrestricted whole-column formula. Increase it when necessary and keep the validation and conditional-formatting ranges aligned.
Explain the rule to collaborators. A short validation message and a note in the header reduce confusion when an editor sees a rejection. Identify which field is the unique key.
Check imported data separately. After a CSV import or bulk paste, use the duplicate highlighting rule or remove-duplicates tool to review the batch. Do not assume that every import preserves the validation setup or has been checked by it.

The menu paths in this guide are for Google Sheets in a web browser. Excel has comparable data validation and conditional-formatting features, but its menus and rule setup differ; the Google Sheets paths above do not describe Excel’s interface.

Troubleshooting

A duplicate is accepted. Open Data > Data validation with a cell in the intended range selected. Confirm the rule applies to that cell, the custom formula references the correct column and starting row, and the invalid-data setting is Reject input. Also check whether an editor pasted data or changed the validation rule.
Every entry is rejected. Confirm that the formula’s first-cell reference matches the top row of the applied range. Check that the allowed-blank condition is present if blanks should be accepted, and ensure the checking range includes the cells where data is stored.
Duplicates are highlighted in the wrong rows. In the conditional-formatting panel, check both the Apply to range setting and the first-row reference in the formula. For a range beginning at A2, the example formula should refer to A2, while the checked range is fixed with dollar signs.
Values that look different are treated as duplicates. COUNTIF comparisons are not case-sensitive, and formatting differences may not distinguish values. Decide whether capitalization should matter. If spaces or other characters are part of the key, standardize the input rather than assuming the visual appearance tells the whole story.
The cleanup removes more or fewer records than expected. Undo immediately if possible, or restore the copy made before cleanup. Run the tool on a copy first, verify the header option, and select only the columns that define a duplicate. When preserving full records, select the whole table so the intended rows stay together.

Frequently Asked Questions

Can Google Sheets block duplicates as someone types?
Yes. A custom-formula data-validation rule set to Reject input can block repeated values entered directly into the validated range. Test it after setting it up and review pasted or imported batches separately.

Can I prevent duplicate email addresses but allow blank cells?
Yes. The example rule includes OR(A2="",...), which accepts a blank or a value that occurs only once. Apply it to the email range and change the column references if your email addresses are elsewhere.

Can duplicates be checked across two columns?
Yes. Use COUNTIFS to count rows where both fields match, as in the product-and-location example above. This treats a repeated pair as a duplicate while allowing either field to repeat on its own.

Does Remove duplicates stop future duplicates?
No. Data > Data cleanup > Remove duplicates cleans existing rows once. Use data validation to reject routine duplicate entries going forward, and use conditional formatting to make repeats visible.

Are uppercase and lowercase values considered different?
Not in the COUNTIF-based examples here; text matching is not case-sensitive. If capitalization has meaning in your identifiers, decide on a consistent input convention and use a comparison method designed for that requirement.

Will the rule catch duplicates in a pasted batch?
Do not rely on validation as the only check for bulk pastes or imports. Pasting can affect rules or introduce values that need review. After a batch change, inspect the highlighted range and run duplicate cleanup on a copy if you need to remove repeated records.

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.