Google Sheets Duplicate Finder: Find and Remove Repeats

Google Sheets has several built-in ways to find duplicates: use conditional formatting to highlight repeated values, a COUNTIF formula to identify them in a helper column, or Data > Data cleanup > Remove duplicates to delete repeated rows. Choose highlighting or a formula when you need to review matches; remove duplicates only after confirming which columns define a duplicate and making a copy of important data.

What counts as a duplicate in Google Sheets?

A duplicate can mean the same value appearing more than once in one column, such as a repeated email address, or a repeated combination of values across a row, such as the same customer and order date. Decide which meaning fits your task before choosing a tool. Two records with the same customer name but different order numbers may be legitimate separate records, while two rows with the same email and order number may be accidental copies.

It also helps to decide whether a match must be exact. A space at the end of a name, inconsistent capitalization, or a different punctuation mark can make two entries look alike to a person but differ as text. Conversely, some spreadsheet comparisons, including COUNTIF, are not case-sensitive, so capitalization alone may not distinguish values. If the data comes from forms, exports, or manual entry, inspect likely matches before deleting anything.

Highlight duplicates with conditional formatting

Conditional formatting is a good first step because it marks repeated values without changing or deleting the data. For example, to highlight every email that appears more than once in column A, select A2:A, then choose Format > Conditional formatting. Under “Format rules,” select “Custom formula is,” enter the formula below, choose a highlight style, and click Done. The range begins in row 2 because row 1 is assumed to contain a heading.

With email addresses in A2:A and a header in A1, use this custom formula:

=AND(A2<>"",COUNTIF($A$2:$A,A2)>1)

The dollar signs keep the search range anchored as the rule evaluates each row; A2 remains relative so the rule checks the current cell. The AND condition prevents empty cells from being highlighted as duplicates. If the values begin in another row or column, change both the applied range and the cell reference to match—for instance, a range starting at B3 should use B3 in the formula.

Conditional formatting flags every occurrence of a repeated value, including its first appearance. That is useful for a visual review, but it does not tell you which instance to keep. To identify only later occurrences, use a helper column instead.

Use COUNTIF to mark later occurrences

A helper column can label repeats while leaving the source data intact. Suppose email addresses are in A2:A and row 1 contains the heading. In B1, add a heading such as “Repeat?” and enter the following formula in B2, then fill it down as far as the data goes:

With emails in A2:A and the first check in B2, use:

=IF(A2="","",COUNTIF($A$2:A2,A2)>1)

The formula checks each email against the list from the first data row through the current row. The first occurrence returns FALSE; a later occurrence returns TRUE; an empty source cell returns a blank. For example, if A2 is sam@example.com, A3 is lee@example.com, and A4 repeats sam@example.com, B2 and B3 return FALSE and B4 returns TRUE. You can filter the helper column for TRUE to review later matches.

This approach is useful when you want to keep the first record and inspect subsequent ones, but it assumes the order of rows matters. If you sort the data, the “first” occurrence can change. Keep all related columns together when sorting so that names, dates, and other details do not become separated from their records.

Find duplicate rows across multiple columns

To flag rows that repeat a combination of fields, compare each relevant column rather than just one. For example, suppose customer names are in A2:A and order dates are in B2:B. If a duplicate means the same customer and date together, add a helper formula in C2 and fill it down:

With customer names in A2:A, dates in B2:B, and the check in C2, use:

=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1

COUNTIFS counts rows in which both conditions match. This formula returns TRUE for a later row with the same customer and date, and FALSE for the first occurrence. Add more range-and-criterion pairs if a duplicate should require a match on additional fields, such as customer, date, and product code. Make sure each pair uses aligned ranges and that the formula starts on the first data row.

Before relying on a multi-column match, check that the chosen fields actually identify a repeated record. A customer and date may occur more than once legitimately if the customer placed multiple orders that day. Including an order ID or product code can make the comparison more precise.

Remove duplicate rows with the data cleanup tool

When you are ready to delete duplicates, select the complete table, including all columns that should stay together. In Google Sheets in a web browser, choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the selected data has a header row and choose the columns that determine whether rows count as duplicates. Then review the result message and check that the number removed makes sense.

For example, if an exported contact list has Name, Email, and Notes columns, you might use Email to identify repeated contacts while selecting the whole table. That way, the surviving contact row retains its associated name and notes. If you compare only Name, two different people with the same name could be treated as duplicates. The tool keeps one instance and removes repeated rows according to the selected comparison columns; it is not a substitute for deciding which record is authoritative.

Make a copy of the sheet or table before cleanup if the data matters, especially when the removed rows may contain useful differences in notes or dates. You can use Edit > Undo immediately after an accidental cleanup, but undo history is not a reliable long-term backup. The built-in cleanup command changes the data, whereas conditional formatting and helper formulas make it easier to review before taking that step.

Common causes of misleading matches

Duplicate checks can produce results that surprise people because data that looks consistent may not be stored consistently. Before deleting flagged records, look at the actual cell values and consider these common issues:

Extra spaces: “Alex Kim” and “Alex Kim ” may appear identical on screen but contain different text. Leading, trailing, or repeated spaces can interfere with exact comparisons.
Different formats: Dates, phone numbers, and IDs may be stored as different types or displayed with different formats. A date displayed as 3/4/2026 can mean different things depending on locale.
Incomplete keys: Matching on a common name or company alone can flag distinct people or transactions. Add a more specific field, such as an email or record ID.
Blank cells: A whole-column rule may evaluate many empty rows. The AND check in the conditional-formatting example suppresses blank matches; review the range and formula if you adapt it.
Case and punctuation: Spreadsheet matching is not always the same as a human’s idea of identity. Treat capitalization, hyphens, and punctuation consistently if they matter to your records.

For messy text, you can create a separate normalized value rather than altering the original. With the original text in A2, a helper cell can remove certain nonprinting characters, trim ordinary surrounding spaces, and convert letters to lowercase:

With the value to normalize in A2, enter this in a helper cell on the same row:

=LOWER(TRIM(CLEAN(A2)))

Compare the normalized helper values, but remember that CLEAN does not remove every unusual space character, such as a nonbreaking space. Normalizing also changes what counts as equal: lowercase conversion intentionally treats uppercase and lowercase letters alike. Keep the original column so you can verify the source information.

Choose the right duplicate-finding method

The best method depends on whether you are diagnosing a problem, reviewing records, or cleaning a finished list. Conditional formatting is quick for a visual scan. A helper formula is better when you need to filter, count, or mark only later appearances. Remove duplicates is the direct option when you have confirmed the comparison fields and want repeated rows gone.

Use conditional formatting for a fast, reversible visual check of one column.
Use a helper formula when you need to identify later instances or compare several fields.
Use Remove duplicates when the criteria are clear and you have reviewed or backed up the data.
Use UNIQUE when you want a separate list of distinct values rather than changing the original table.

Google Sheets’ UNIQUE function returns distinct rows or values from a range. For example, with source values in A2:A, entering =UNIQUE(A2:A) in an empty cell creates a distinct list from that column. It is a useful, non-destructive alternative for summaries, but it does not explain which source rows were repeated, and the output is not a replacement for auditing the original records.

Costs, add-ons, and data safety

The conditional-formatting rules, COUNTIF and COUNTIFS formulas, UNIQUE function, and Remove duplicates command are built into Google Sheets; you do not need a duplicate-finder add-on for ordinary lists. Add-ons may offer specialized matching or larger-scale workflows, but check their permissions, data handling, pricing, and whether they can access or modify spreadsheet contents before granting access. A third-party tool is rarely necessary just to identify repeated email addresses or rows.

For sensitive or business-critical data, work on a copy and limit access to the people who need it. A duplicate finder can identify records that meet a matching rule, but it cannot determine whether two records truly refer to the same person, whether the older record is obsolete, or which conflicting details are correct. Use the flagged results as a review queue, not as proof that a record should be discarded.

Frequently Asked Questions

How do I find duplicates in Google Sheets without deleting them?

Select the range, choose Format > Conditional formatting, and use a COUNTIF custom formula to highlight repeated values. For a reviewable list, use a helper column with COUNTIF or COUNTIFS. Neither method deletes the source data.

How do I highlight duplicates in one column?

For values beginning in A2, apply conditional formatting to A2:A and use =AND(A2<>"",COUNTIF($A$2:$A,A2)>1) as the custom formula. Change A to the column you are checking and keep the row reference aligned with the first row in the applied range.

Can Google Sheets find duplicate rows based on two columns?

Yes. Use COUNTIFS in a helper column to check whether the current row’s values in both columns have appeared together earlier. For example, compare customer and date fields when both must match for a row to count as a duplicate.

Does Remove duplicates delete the first copy or the later copies?

The cleanup tool removes repeated rows and retains one occurrence. Before running it, select the full table and specify the columns that define a match. Make a copy first if you need to preserve every original row for review.

Why are two cells that look the same not being treated as duplicates?

They may contain extra spaces, different punctuation, different underlying values, or invisible characters. Compare the cell contents, clean or normalize them in a helper column, and make sure your formula’s range and starting row are correct.

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.