Find Duplicates in Google Sheets: Methods That Work

To find duplicates in Google Sheets, select the cells, then choose Format > Conditional formatting and use a COUNTIF custom formula to highlight repeated values. To permanently remove repeats instead, use Data > Data cleanup > Remove duplicates—but make a copy first, because that action changes your data.

Choose the right way to find duplicates

“Duplicate” can mean more than one thing. You might want to flag every value that appears more than once, identify only the second and later occurrences, find repeated whole rows, or delete redundant records. The best method depends on which result you need and whether the original data must remain unchanged.

For a quick visual check, conditional formatting is convenient: it marks matches in place and updates when the data changes. A helper column with COUNTIF gives you a count or a filterable label. Remove duplicates is useful for cleanup, but it removes records rather than merely identifying them. A formula such as UNIQUE can create a separate list without altering the source.

Use conditional formatting to see repeats while keeping all rows.
Use a helper column when you need counts, filters, or a repeatable review process.
Use Remove duplicates only when you are ready to delete repeated records.
Use UNIQUE to produce a separate deduplicated list.

Highlight repeated values with conditional formatting

For example, if customer emails are in A2:A, you can highlight every nonblank email that appears more than once. Select A2:A, open Format > Conditional formatting, and under “Format rules” choose “Custom formula is.” With the selected range beginning at A2, enter this formula:

With emails in A2:A and the conditional-formatting range set to A2:A, use =AND(A2<>"",COUNTIF($A$2:$A,A2)>1). Choose a fill color under “Formatting style,” then select Done.

The dollar signs keep the list’s starting column and row fixed as Sheets evaluates each cell; A2 remains relative so the rule checks the current row. The blank check prevents empty cells from being highlighted as a repeated value. If you do want blanks included in the check, remove the AND condition, although empty entries are rarely useful duplicates.

This rule highlights every instance of a repeated email, including the first one. To highlight only the second and later occurrences, use a running count instead:

With emails in A2:A and the formatting range set to A2:A, use =AND(A2<>"",COUNTIF($A$2:A2,A2)>1).

As the rule moves down the column, its count range expands from A2 to the current row. The first appearance has a count of one and is not highlighted; later appearances have a count greater than one. This is useful when you want to preserve the original record and spot only the entries that follow it.

Count duplicates with a helper column

A helper column makes the result visible as data, not just color. If your list is in A2:A, put a heading such as “Count” in B1. Enter the following in B2 and fill it down beside the records:

With values in A2:A and the formula entered in B2, use =COUNTIF($A$2:$A,A2).

A result of 1 means the value appears once; 2 or more means it appears repeatedly. You can filter the helper column for values greater than 1 to review just the repeated entries. To label them instead of showing a number, use an IF formula:

With values in A2:A and the label formula entered in B2, use =IF(A2="","",IF(COUNTIF($A$2:$A,A2)>1,"Duplicate","Unique")).

Copy the formula down for the rows you need, or use the fill handle. The blank check leaves empty source cells unlabeled. Unlike conditional formatting, a helper column can be sorted or filtered, but take care when sorting: include the whole record range so names, dates, and other fields stay with the correct value.

Find duplicates based on more than one column

Sometimes a value is not a duplicate by itself. Two orders may share the same product, for example, while having different order IDs. A record may count as repeated only when both its customer and email match. In that case, compare the combination of fields rather than just one column.

Suppose customer names are in A2:A and email addresses are in B2:B. To count rows where both fields match the current row, enter this in C2 and fill down:

With names in A2:A and emails in B2:B, use =COUNTIFS($A$2:$A,A2,$B$2:$B,B2).

A result above 1 means that the name-and-email combination occurs more than once. For conditional formatting across the records, select the relevant range, such as A2:B, and use a custom formula that checks the two columns together:

With names in column A, emails in column B, and the format range A2:B, use =AND($A2<>"",$B2<>"",COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1).

The dollar signs before the column letters make both fields stay in their respective columns while the row number adjusts. This rule requires both fields to be nonblank; modify the blank conditions if incomplete records should also be compared. For whole-row matching across several fields, add each relevant column as another range-and-criterion pair in COUNTIFS.

Remove duplicates without losing track of your data

Google Sheets has a built-in cleanup tool for deleting repeated records. First, make a copy of the sheet or duplicate the tab if you may need the original. Then select the data range, including all columns that belong to each record, and choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the data has a header row and select the columns Sheets should use to identify duplicates. Confirm the action to remove the repeats.

The tool keeps one instance and removes other rows it considers duplicates according to the columns you selected. If you select only the email column, rows with the same email can be treated as duplicates even if their names or other fields differ. If you select all record columns, two rows must match across those selected fields to be considered repeated. Check the selection carefully: using too few columns can remove legitimate records, while including a field that differs on every row can prevent matches from being found.

Removal is not the same as highlighting. The cleanup action changes the selected data, and you should not rely on undo as your only backup. Keeping an untouched source tab is a simple way to compare the cleaned result or restore an accidentally removed record.

Create a separate list with UNIQUE

If you need a deduplicated list but want to retain the source, put a formula in an empty area or a separate sheet. For values in A2:A, enter:

With source values in A2:A and an empty output area beginning at D2, use =UNIQUE(A2:A).

Sheets returns a list of distinct values in the output area, retaining their first-seen order. The formula result updates as the source changes, and the cells it needs to fill must be clear. UNIQUE is not a report of which entries were duplicates: it returns one instance of each distinct value. If you need to identify the repeated records themselves, use a count or conditional-formatting rule instead.

To see whether the list contains repeats, compare its number of nonblank entries with the number of unique values, or use a helper count. Avoid putting the output formula inside the source range, since the result needs room to expand and should not overwrite the data being examined.

Common problems and how to avoid them

A formula can be correct and still miss a match if the source values are inconsistent. Before removing anything, check how the data was entered and whether the fields represent the same kind of information.

Extra spaces: “sam@example.com” and “sam@example.com ” may not behave as identical text. A helper column using TRIM can clean ordinary leading and trailing spaces; review the cleaned values before replacing source data.
Capitalization: COUNTIF in Google Sheets is not case-sensitive, so values that differ only by letter case are generally treated as matches. If case is meaningful for your task, use a more deliberate comparison rather than assuming the basic count distinguishes it.
Numbers stored as text: A numeric value and text that looks like a number can behave differently in comparisons. Standardize the column’s data type before drawing conclusions.
Wrong range start: A custom formula is evaluated relative to the first cell in “Apply to range.” If the range starts at A2, a row-based reference should usually start with row 2 as well.
Partial records: Comparing only a name can mark two different people with the same name as duplicates. Use a stable identifier, such as an account ID, or compare a combination of fields.
Formula output: Cells that look identical may contain different underlying values, such as dates with different times or numbers with extra precision. Inspect the actual data and standardize it if those distinctions are irrelevant.

For large sheets, whole-column references are convenient, but rules and formulas may be easier to manage if you use a realistic bounded range, such as A2:A5000. Keep the range large enough for new rows or update it as the data grows. A filter view can also help reviewers inspect duplicate labels without changing what other collaborators see.

Google Sheets compared with Excel

The core approaches are familiar in both spreadsheet programs: conditional formatting, COUNTIF-style formulas, and a remove-duplicates command. This guide uses Google Sheets in a web browser. Its menu paths are Format > Conditional formatting and Data > Data cleanup > Remove duplicates. Excel for Microsoft 365 has its own ribbon locations and dialog wording, so do not assume that a Sheets menu path applies there.

Google Sheets formulas can also be shared and recalculated in a collaborative file, while the built-in removal action edits the shared sheet. If several people are working in it, coordinate before deleting rows and consider using a separate review tab or a copy. A formula-generated unique list is often the safer option when other collaborators still need the original records.

Frequently Asked Questions

How do I highlight duplicates in Google Sheets?
Select the cells, choose Format > Conditional formatting, set the rule to “Custom formula is,” and use a COUNTIF rule such as =COUNTIF($A$2:$A,A2)>1 for values in A2:A. Add a blank check if empty cells should be ignored.

How do I find only the second occurrence?
Use a running COUNTIF rule, such as =COUNTIF($A$2:A2,A2)>1, in conditional formatting. The first match is not highlighted; later occurrences are. Add an AND condition to exclude blank cells.

Can Google Sheets remove duplicates automatically?
Yes. Select the records and choose Data > Data cleanup > Remove duplicates. Choose whether the range has a header and which columns define a duplicate. Make a copy first if you need to preserve the original data.

How do I find duplicate rows instead of duplicate cells?
Compare all fields that define a repeated record. For two fields, COUNTIFS can count rows where both values match; add further range-and-criterion pairs for additional fields. Formatting can then mark the full row based on that count.

Does UNIQUE delete the original data?
No. UNIQUE returns a separate list of distinct values wherever its formula is placed. It does not remove or edit the source range, but its output needs an empty area to expand into.

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.