Checking Duplicates in Google Sheets: A Practical Guide

To check duplicates in Google Sheets, select the cells you want to check and use Format > Conditional formatting with a custom formula. If your values are in A2:A, enter =AND(A2<>"",COUNTIF($A$2:$A,A2)>1) to highlight every nonblank value that appears more than once. To remove duplicates instead, use Data > Data cleanup > Remove duplicates; review the selected range and its header setting before confirming.

Choose the right way to check

“Check duplicates” can mean several different things. You might want to spot repeated entries while keeping the data, count how often a value appears, flag only later copies, or permanently remove repeated rows. These are different jobs, so choose the method that fits before changing the sheet.

Highlight: Best for reviewing a list visually while keeping all rows.
Count or flag: Useful when you want a repeat count or a review column you can filter.
Remove: Suitable when you have confirmed which columns define a duplicate and want to keep one copy.

A duplicate is only meaningful in relation to the data you compare. Two rows with the same email address may be duplicates even if their names differ; two rows with the same name may represent different people. Decide whether the check applies to one column, a combination of columns, or an entire row.

Highlight duplicates with conditional formatting

Conditional formatting is usually the safest first step because it marks matches without deleting or rearranging information. The example below checks a single column and highlights every nonblank value that occurs at least twice.

Select the range to check, such as A2:A. Leave the header out of the selection if row 1 contains a heading.
Choose Format > Conditional formatting.
In the Conditional format rules panel, choose Custom formula is for the format rule.
With values in A2:A, enter =AND(A2<>"",COUNTIF($A$2:$A,A2)>1). Choose a fill color and select Done.

The formula’s A2 reference adjusts for each row, while $A$2:$A stays fixed as the range being searched. The AND condition skips empty cells, which would otherwise be treated as repeated blank values. If your data ends at row 500, you can use $A$2:$A$500 rather than checking the entire column.

To highlight only the second and later occurrences, so the first copy remains unmarked, use a growing comparison range. With values in A2:A, set the custom formula to =AND(A2<>"",COUNTIF($A$2:A2,A2)>1). For example, if “North” appears in A2, A5, and A9, this rule leaves A2 unmarked and highlights A5 and A9. This can make a long list easier to review before cleaning it.

Count repeats or create a review flag

A helper column makes duplicate checks easier to filter, sort, and explain to collaborators. It can also show how many times each entry occurs, rather than only coloring the cells. Put a heading such as “Occurrences” in B1, then enter the formula below in B2 and fill it down. With the values being checked in A2:A, use =COUNTIF($A$2:$A,A2). If A2 contains a value that occurs three times in the checked range, the result is 3.

To label whether a value repeats, use a second helper column. With values in A2:A, enter this in B2 and fill down: =IF(A2="","",IF(COUNTIF($A$2:$A,A2)>1,"Duplicate","Unique")). The formula leaves blank source cells blank, labels repeated entries “Duplicate,” and labels values appearing once “Unique.” You can then use the filter controls to show only the rows needing attention.

COUNTIF is not case-sensitive: it treats “Maple” and “maple” as matches. It also recognizes * and ? as wildcard characters in criteria, which matters if those symbols are part of the actual text you are checking. For ordinary names, IDs, and email addresses, this is rarely an issue, but unusual data may need a more specialized approach.

Check duplicates across multiple columns

Sometimes a record is a duplicate only when two or more fields match together. For instance, a customer’s name may appear on several orders, but the same name plus the same email address may identify a repeated contact record. COUNTIFS checks multiple conditions at once.

With names in A2:A and email addresses in B2:B, enter the following in C2 and fill down to flag repeated name-and-email pairs: =AND(A2<>"",B2<>"",COUNTIFS($A$2:$A,A2,$B$2:$B,B2)>1). The result is TRUE for each row whose combination appears more than once. The two nonblank checks prevent rows missing either field from being flagged as duplicates by virtue of matching other incomplete rows.

If the rule is based on email alone, check column B alone rather than requiring the name to match. Conversely, if a duplicate means that every field in a row matches, include every relevant column as a COUNTIFS condition. Be deliberate about which fields identify a record: adding a date or notes field can make otherwise identical records appear unique.

Remove duplicates without losing the wrong rows

Google Sheets includes a built-in cleanup command. Make a copy of the tab or file first if the original data matters, then select the table or range you intend to clean. Choose Data > Data cleanup > Remove duplicates. In the dialog, confirm whether the data has a header row and select the columns that define a duplicate. Click Remove duplicates only after checking those choices.

If you select just the email column, Sheets removes repeated email entries from that selection. If you select an entire table and choose all its columns, it removes rows that match across all selected columns. Those outcomes are not interchangeable: selecting the wrong columns can discard a row that contains other useful information. The cleanup command changes the data, so use conditional formatting or a helper column first when you need to inspect potential matches.

Removing duplicates is also not the same as deleting every value that has a match. The usual aim is to retain one instance and remove additional matching entries. If your task is to find all members of duplicate groups and delete the whole group, identify and filter those groups first, then review the rows before deletion.

What counts as the same value?

Many apparent duplicate problems are really inconsistencies in how data was entered. A trailing space in “Avery ” can make it differ from “Avery” in a direct comparison. Extra spaces, line breaks, inconsistent punctuation, and different spellings can all prevent a match. A cell’s displayed format can also be misleading: two dates may look alike while their underlying values differ, or a number may be stored as text in one row and as a number in another.

Before removing data, inspect suspicious pairs and normalize values when appropriate. For example, TRIM can remove extra ordinary spaces from text, but it does not resolve different spellings or decide whether two records refer to the same person. If using a cleanup column, keep the original values until you have checked the normalized results. Avoid applying broad transformations to identifiers where spaces or punctuation may be significant.

Also distinguish duplicates from legitimate repeats. A product name repeated across an order list is not automatically an error; repeated customer IDs may be expected in a transaction table. Ask what one row represents and which fields should be unique. That definition is more important than the color or formula used to find matches.

Troubleshoot rules that miss or over-highlight values

If the wrong cells turn colored, check that the conditional-formatting range starts on the same row referenced by the formula. For a range A2:A, the custom formula should normally refer to A2, not A1 or A3. Also check whether the formula’s search range includes the intended records and excludes headings or unrelated data.

Blanks are highlighted: Add a nonblank test such as A2<>”” to the custom formula.
Some duplicates are not found: Look for extra spaces, inconsistent data types, or different punctuation.
Too many matches appear: Confirm that the rule uses the right columns and that the selected range does not include unrelated records.
The rule stops applying to new rows: Extend the conditional-formatting range or use an open-ended range such as A2:A.
Only some rows seem affected: Review the starting row and relative references in the formula.

When a rule looks unexpected, test it on a few known examples in a spare helper column. That makes it easier to tell whether the issue is the formula, the selected range, or the source data. It is especially useful before using a destructive cleanup command.

Google Sheets and Excel: a useful distinction

These steps use Google Sheets in a web browser. Google Sheets uses Format > Conditional formatting and Data > Data cleanup > Remove duplicates. Excel has its own conditional-formatting and duplicate-removal tools, with different ribbon paths and interface details; do not assume that a Google Sheets menu path applies in Excel. The COUNTIF and COUNTIFS examples are also common spreadsheet functions, but the instructions here describe how to apply them in Sheets.

For collaboration, highlighting and helper columns are often preferable to immediate removal because teammates can inspect the records and agree on the duplicate definition. If you do clean the sheet, communicate which columns were used to decide what counted as a match. That context helps prevent someone from reintroducing duplicates or mistaking valid repeated transactions for errors.

Frequently Asked Questions

How do I highlight duplicates in Google Sheets?

Select the range, choose Format > Conditional formatting, set the rule to Custom formula is, and enter a COUNTIF formula. For values in A2:A, =AND(A2<>"",COUNTIF($A$2:$A,A2)>1) highlights every nonblank value that appears more than once.

Can Google Sheets show only the second copy?

Yes. Use a running range in the custom formula. For a range beginning at A2, =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) leaves the first occurrence unmarked and flags later occurrences.

Does Remove duplicates delete the first entry too?

The command is intended to remove repeated records while retaining one instance of each matching record. Check the selected columns and header setting first, because those choices determine which rows Sheets considers duplicates.

Why does Sheets not recognize two entries as duplicates?

The cells may differ because of leading or trailing spaces, punctuation, spelling, or a number stored as text in one cell. Check the underlying values and standardize the data only after confirming that the differences are not meaningful.

Are duplicate checks case-sensitive?

COUNTIF and COUNTIFS checks are generally case-insensitive, so values such as “Maple” and “maple” count as matches. If capitalization distinguishes records in your workflow, use a case-sensitive comparison method and test it on sample rows before cleaning data.

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.