Google Sheets Look for Duplicates? Find Them Fast

How-to basics · Updated November 21, 2026

Yes. In Google Sheets, use Format > Conditional formatting to highlight duplicate values, or Data > Data cleanup > Remove duplicates to delete repeated rows. A formula such as =COUNTIF($A$2:$A,A2)>1 can also flag repeats while leaving your data intact.

Three ways to check for duplicates

The best method depends on whether you want to spot, count, or remove repeat entries. Conditional formatting is convenient when you want to scan a column visually. A formula is more flexible: it can mark only later occurrences, count how often an item appears, or check a combination of fields. The built-in cleanup command is quickest when you have reviewed the data and are ready to remove duplicate rows.

For example, suppose customer email addresses are in column A, with a header in A1 and records beginning in A2. You can apply a color rule to the column, put a duplicate-check formula in an unused column, or select the relevant table and run Remove duplicates. These approaches answer slightly different questions: “Which values repeat?”, “Which rows are repeats?”, and “Can I delete the repeats?” Decide which question matters before changing the sheet.

Highlight repeated values with conditional formatting

To highlight every occurrence of a repeated value in one column, select the data cells, such as A2:A, and choose Format > Conditional formatting. In the side panel, set “Format cells if” to “Custom formula is,” enter the formula below, and choose a formatting style:

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

This assumes the values to check are in A2:A. The first reference, A2, is relative so the rule adjusts for each row; the dollar signs keep the lookup range anchored. AND excludes blank cells, so empty rows are not colored as duplicates. If your data starts in a different row or column, update the formula and the “Apply to range” setting to match.

COUNTIF is not case-sensitive, so “Lee@example.com” and “lee@example.com” are treated as matching by this rule. Spaces can matter: “Lee” and “Lee ” may not behave as you expect when visually scanning the sheet. Conditional formatting only changes how matching cells look; it does not alter, sort, or delete any data. That makes it a useful first check before cleanup.

Use a formula to label duplicates

A helper column is useful when you want a persistent label you can filter or review. With the values in A2:A and a header in A1, put this formula in B2 and fill it down:

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

The result is TRUE for every row whose value appears more than once in column A, including the first occurrence. To label only the second and later occurrences, use this version in B2 and fill down instead:

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

That formula counts from the first data row through the current row. The first appearance returns FALSE; a subsequent matching value returns TRUE. For a readable label instead of TRUE or FALSE, with the same layout, use =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First or unique"). You can then filter the helper column to show only the rows marked Duplicate. If you add records later, remember to extend a filled-down formula unless you use an array-based setup.

Check duplicates across a complete row

Two records may share one field but still be different records. For example, two people can have the same last name, or a product can appear in separate regions. If a duplicate means that several fields match together, compare those fields rather than checking just one column.

Suppose columns A, B, and C contain a name, email, and region, with data beginning in row 2. To flag rows where all three values have appeared together before, enter this in D2 and fill down:

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

COUNTIFS checks the three corresponding criteria at once. This version marks later copies of the same combination, not the first row. Change the listed columns to reflect the fields that define a duplicate in your dataset. If order numbers should be unique regardless of customer or region, use only the order-number column; if a duplicate requires the same order number and item, check both.

Remove duplicates with Data cleanup

When you are ready to remove repeated records, select the whole table, including every column that should stay together. Then choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the selected data has a header row and choose the columns Sheets should use to decide whether rows are duplicates. Confirm the action after checking those settings.

If you select only the email column, Sheets evaluates duplicates using that column alone. If you select an entire table and include all its columns, it removes rows that match across the selected columns. You can also select only specific columns in the dialog when those fields define a duplicate. The command removes duplicate rows from the selected data; it is not just a visual highlight. Review the result message and the remaining records afterward.

Make a copy of the sheet or preserve the original data before cleanup if the rows are important. This is especially sensible for customer lists, imported transactions, or records with formulas and notes. Removing duplicates can change row alignment with data outside the selected range, so include related columns in the selection when they belong to the same records. If you remove something by mistake, undo promptly with Edit > Undo or Ctrl+Z on Windows; keeping a copy is safer than relying on undo.

Why duplicate checks can miss or overflag records

A spreadsheet can contain values that look the same but are not identical, or values that differ in presentation but represent the same information. Before deleting anything, check common causes of confusing results:

  • Extra spaces: Leading, trailing, or repeated spaces can make imported text inconsistent. TRIM can help clean ordinary spaces; test a cleaned copy before replacing original values.
  • Different formats: Dates, numbers, and text can display similarly while being stored differently. Check the underlying cell values and formatting if an expected match is not found.
  • Case differences: COUNTIF treats text matching without regard to capitalization. If letter case is meaningful to your task, do not assume this check distinguishes it.
  • Partial matches: A rule applied to last names alone will flag people with the same surname even if their other details differ. Select the fields that genuinely define a duplicate.
  • Blank cells: A simple count rule can include blanks. Excluding empty values with an AND condition avoids highlighting empty rows.
  • Formula results: Cells containing formulas may display the same result even though their formulas differ. Decide whether you care about matching displayed values or matching the underlying formulas.

Cleaning text can improve matching, but it can also change meaningful distinctions. For instance, removing spaces may be appropriate for an email list but not for every identifier. Compare a cleaned helper column with the source before using it as the basis for deletion.

Choose a method that fits the task

Use conditional formatting when you want a quick visual audit and expect to inspect the matches yourself. Choose a helper formula when the result needs to be filtered, counted, or checked repeatedly as new rows arrive. Use Remove duplicates when the criteria are clear and deleting repeated rows is the intended outcome.

A cautious workflow for a large or valuable sheet is:

  1. Make a copy of the tab or file.
  2. Choose the columns that define a true duplicate.
  3. Highlight or label the matches first, then inspect examples and edge cases.
  4. Remove duplicates from the copy, selecting the complete related table.
  5. Compare the cleaned result with the source and confirm important records remain.

In Excel, the general ideas are similar, but the menus and some formula behavior or available features can differ. The menu paths and instructions here are for Google Sheets in a web browser.

Alternatives when you want a separate clean list

If you want to display unique entries without deleting anything, Google Sheets has the UNIQUE function. With records in A2:C, enter =UNIQUE(A2:C) in an empty area to return distinct rows from that range. Keep enough empty space below and to the right for the results to expand. UNIQUE produces a separate result; it does not clean or overwrite the source data.

For a quick one-column list, the same approach works with a single column, such as =UNIQUE(A2:A). This is handy for building a list of distinct categories, but it is not a substitute for reviewing suspected duplicate customer records. If two rows differ in any included field, they are distinct rows even if one particular column matches.

Frequently Asked Questions

How do I find duplicates in Google Sheets without deleting them?
Apply conditional formatting with a COUNTIF custom formula, or use a helper column that returns TRUE for repeated entries. Both methods flag matches while preserving the original rows.

Does Google Sheets have a built-in duplicate remover?
Yes. Select the data and choose Data > Data cleanup > Remove duplicates. Set the header option and comparison columns carefully because the command removes rows from the selected data.

How do I find duplicate rows instead of duplicate cells?
Use COUNTIFS to compare the fields that together define a record, or use Remove duplicates with the relevant columns selected. Checking only one column finds repeated values in that column, not necessarily repeated whole records.

Can Google Sheets highlight only the second copy?
Yes. Apply a conditional-formatting custom formula such as =COUNTIF($A$2:A2,A2)>1 to A2:A. Because the range grows as the rule moves down, it flags later occurrences but not the first.

Why does Sheets say two entries are duplicates when their capitalization differs?
COUNTIF-based matching is case-insensitive, so capitalization alone does not make text values unique for this check. Review those records and use a case-sensitive approach if capitalization is meaningful for your data.

Related

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.