Find Duplicates in Google Sheets: Easy Methods

The easiest way to find duplicates in Google Sheets is to select the cells, choose Format > Conditional formatting, and use a custom formula to highlight repeated values. For a simple one-column check starting in A2, use =COUNTIF($A$2:$A,A2)>1; it marks every value that appears more than once.

Choose the right way to find duplicates

Highlighting is a good first step because it shows repeats in place without changing or deleting your data. If you want a separate list of distinct values, use a formula instead. If you are ready to remove repeated records, use the built-in Remove duplicates tool only after checking which columns define a duplicate.

The right choice depends on what you mean by duplicate. You might mean an identical value in one column, a repeated combination such as the same name and email, or an entirely repeated row. Those are different checks: finding two identical email addresses does not automatically mean the corresponding rows are duplicates if other information differs.

To see duplicates in the original data: use conditional formatting.
To label repeats in a helper column: use COUNTIF or COUNTIFS.
To make a distinct list: use UNIQUE, optionally with FILTER.
To delete repeated records: use Data > Data cleanup > Remove duplicates after making a copy.

Highlight repeated values with conditional formatting

This method is convenient when you want to scan a column and decide what to do next. In Google Sheets in a web browser, select the range you want to check, then open Format > Conditional formatting. In the side panel, set Apply to range to the chosen cells, change Format cells if to Custom formula is, enter the formula below, and choose a formatting style.

With the values to check in A2:A, and the conditional-formatting rule applied to A2:A, enter =COUNTIF($A$2:$A,A2)>1. The dollar signs keep the search range anchored at column A and row 2 as the rule is applied down the column; A2 changes to A3, A4, and so on. The rule highlights every occurrence of a repeated value, including the first one.

If row 1 contains a heading, start the applied range at A2 so the heading is not included. You can use the same approach on another column by changing both references. For example, for values in C2:C and a rule applied to C2:C, use =COUNTIF($C$2:$C,C2)>1.

To highlight only the second and later occurrences, apply the rule to A2:A and use =COUNTIF($A$2:A2,A2)>1. Because the ending cell in the count range moves with each row, the first occurrence is not counted as a repeat until a later row contains the same value. This is useful for reviewing incoming entries while leaving the original record unmarked.

Use a helper column to label repeats

A helper column can make duplicates easier to filter, sort, or count. Add a heading such as Duplicate? in B1. With the values to check in A2:A and the first result in B2, enter =COUNTIF($A$2:$A,A2)>1 in B2, then fill the formula down. Each row displays TRUE if its value occurs more than once in the checked range, and FALSE otherwise.

To leave blank cells unlabelled, with the same data layout and the formula in B2, use =AND(A2<>"",COUNTIF($A$2:$A,A2)>1). This is helpful when a column contains empty rows: otherwise, blank cells can be treated as repeated blank values. If you want to label only later occurrences, use =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) instead. Copy the formula downward, or double-click the fill handle when adjacent data makes Sheets’ suggested fill range appropriate.

For a duplicate defined by two fields, use COUNTIFS. With names in A2:A and email addresses in B2:B, and a helper formula in C2, enter =COUNTIFS($A$2:$A,A2,$B$2:$B,B2)>1. It returns TRUE when that name-and-email combination appears more than once. If email alone determines whether a record is repeated, check column B alone instead; including the name would make two records with the same email but different names count as different combinations.

Create a separate list of unique values

Google Sheets’ UNIQUE function returns distinct entries from a range, which can be useful when you want a clean list rather than highlighted cells. With source values in A2:A and the result formula entered in C2, use =UNIQUE(A2:A). The results expand down from C2, generally keeping the first-seen order. Leave the cells below C2 clear so the output has room to expand.

If the source column contains blank cells and you do not want a blank in the output, use FILTER with UNIQUE. With source values in A2:A and the result formula in C2, enter =UNIQUE(FILTER(A2:A,A2:A<>"")). FILTER removes blank source cells before UNIQUE creates the list. A formula-generated list is a view of the source data, not a deletion of duplicates: changes to the source can change the result.

UNIQUE can also compare rows across multiple columns. With a set of records in A2:B and the output formula in D2, enter =UNIQUE(A2:B). A row is considered distinct when its combination of values differs. This is useful for a list of unique name-and-email pairs, but it does not identify which source rows were repeated; use a helper formula or conditional formatting for that.

Decide what counts as the same value

A duplicate check compares the cell contents according to the function or tool being used. In practice, data that looks identical on screen may not match as expected. For example, “North” and “North ” can differ because the second entry has a trailing space. A copied value may also contain an unexpected line break or other invisible character.

Google Sheets’ COUNTIF criteria matching is not case-sensitive, so entries such as apple and Apple are generally treated as a match for this kind of check. If capitalization matters, a basic COUNTIF rule is not enough to distinguish the entries; make a cleaned comparison key or use a more specific formula. Similarly, decide whether a phone number with punctuation should count as the same as the same digits without punctuation before removing or merging records.

For a simple cleanup in a helper column, TRIM removes leading, trailing, and repeated ordinary spaces from text. With raw values in A2:A and a cleaned value formula in B2, use =TRIM(A2) and fill down. Compare the cleaned values in column B rather than editing the original column immediately. If you need to remove nonprinting characters too, with the same layout use =TRIM(CLEAN(A2)). CLEAN addresses certain nonprinting characters, but it does not guarantee that every unusual whitespace character has been removed; inspect suspicious examples rather than assuming all visually similar text is identical.

Check duplicates across whole rows or selected fields

When a record has several columns, first decide which fields identify a record. For example, two rows with the same customer name may be legitimate if they have different email addresses. Conversely, a repeated order ID might be enough to flag a likely duplicate even if another field has been edited.

To highlight repeated combinations of two fields, select the range to format, such as A2:B, then choose Format > Conditional formatting and set the rule to Custom formula is. With names in A2:A and emails in B2:B, enter =COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1. The anchored columns keep the check tied to A and B while the row references adjust as the formatting rule moves down. If you apply this rule across a wider row, check that the references correspond to the first row of the applied range.

For whole-row duplicates, include every column that should match in the comparison. A difference in any included field means the rows are not exact duplicates under that definition. Before deleting rows, it is often safer to add a helper column that flags repeated IDs or repeated field combinations, then review the associated records for conflicting information.

Remove duplicates only after checking the result

Google Sheets includes a built-in removal tool: select the data range, then choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the selected data has a header row and choose which columns should be checked. If all selected columns are checked, Sheets looks for repeated rows across those columns. If only one column is checked, rows with the same value in that column can be treated as duplicates even when other fields differ.

The tool removes duplicate rows from the selected data and keeps one occurrence; it is not merely a visual highlight. Make a copy of the sheet or duplicate the tab before running it, especially if the spreadsheet contains important records. Review the selected columns carefully: choosing too few can remove distinct records, while choosing too many can fail to catch records that differ only in a nonessential field. After removal, check the remaining row count and a few representative records.

For a reversible review process, use a helper column or a separate UNIQUE output first. That lets you inspect what would be considered repeated before changing the source. Also make sure the selected range covers the related columns in each record; checking only one column of a table can create a mismatch between deleted values and the other fields in their rows.

Common problems and practical checks

If nothing is highlighted, confirm that the conditional-formatting formula starts with the first row in the applied range. For a range beginning at A2, the relative reference should begin with A2; if the rule applies from A3 but still refers to A2, the results can be offset. Check that the formula is entered under Custom formula is, and that the range includes the cells you intend to inspect.

If many blank cells are highlighted, add a nonblank condition such as the AND example above. If some apparent matches are missed, check for extra spaces, line breaks, inconsistent data types, or a range that stops before the later entries. A formula that checks only A2:A100 will not find a matching value in A150. Using an open-ended range such as A2:A can include later entries, though a bounded range may be preferable in a very large sheet.

Finally, distinguish a repeated value from a repeated record. A duplicated product name may be expected in an order list, because many orders can include the same product. A repeated transaction ID may be much more significant. Defining the key fields before checking prevents a technically correct duplicate rule from answering the wrong question.

Frequently Asked Questions

Can Google Sheets automatically highlight duplicates?

Yes. Select the range, open Format > Conditional formatting, choose Custom formula is, and enter a COUNTIF rule such as =COUNTIF($A$2:$A,A2)>1 for values in A2:A. The highlight updates as the values in the range change.

How do I find duplicates in two columns together?

Use COUNTIFS to count matching combinations. For names in A2:A and emails in B2:B, =COUNTIFS($A$2:$A,A2,$B$2:$B,B2)>1 flags combinations that occur more than once. Apply it as conditional formatting or put it in a helper column.

Does Remove duplicates delete my data?

It removes repeated rows from the selected range rather than simply marking them. Make a copy first, and verify the header and comparison columns in the dialog before confirming.

How can I keep one copy of each value without deleting the source?

Use UNIQUE in an empty area or another sheet. For example, with source values in A2:A, =UNIQUE(A2:A) returns a distinct list while leaving the original cells unchanged.

Why are duplicate values not being detected?

Check the applied range and formula row, then inspect entries for extra spaces, line breaks, or other differences that are hard to see. Also confirm that the comparison includes the fields that define a duplicate and that the range reaches all relevant rows.

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.