Detect Duplicates in Google Sheets: A Practical Guide

To detect duplicates in Google Sheets, select the cells you want to check, choose Format > Conditional formatting, and use a custom formula such as =COUNTIF($A$2:$A,A2)>1 when the values to check are in A2:A. This highlights every occurrence of a repeated value; you can also use formulas to mark repeats, identify duplicate rows, or remove duplicates with Google Sheets’ data-cleanup tool.

What counts as a duplicate?

A duplicate is a value or record that repeats according to the rule you choose. That rule matters: two rows might be duplicates because their email addresses match, because every field matches, or because a combination of fields matches. Decide what makes a record unique before highlighting or deleting anything.

For example, a customer list might contain two records for the same person with different phone numbers. If email address is the identifier, those records are duplicates for your purpose even though the rows are not identical. In an inventory list, you might instead define a duplicate by product code and warehouse, while allowing the same product code to appear at another warehouse.

Google Sheets does not know which records are accidental. It follows the range and criteria you specify. A repeated value can be legitimate, such as the same product appearing on several order lines, so detecting a match is not the same as deciding to delete it.

Highlight repeated values with conditional formatting

Conditional formatting is a useful first step because it marks matching cells without changing the data. To highlight repeated entries in one column:

Select the data range, such as A2:A100. Leave the header out of the selection.
Choose Format > Conditional formatting.
In the side panel, check that the range is correct. Under Format rules, choose Custom formula is.
With the values in A2:A100, enter =COUNTIF($A$2:$A,A2)>1.
Choose a fill or text style, then select Done.

Here the counted range begins at row 2, so a header is not treated as a value. The dollar signs keep the lookup range anchored as the formatting rule evaluates each cell, while A2 changes to A3, A4, and so on. The rule counts how many times each value occurs and highlights it when the count is greater than one. It highlights the original and later copies, not just the later entries.

If you want to highlight a whole row based on a repeated value in column A, select A2:D100 instead and use =COUNTIF($A$2:$A,$A2)>1. The column reference is locked, so each row is checked using its A-column value. Change the selected range and formula references to fit your sheet.

Mark only the later occurrences

Sometimes you want to keep the first appearance visible and flag only subsequent copies. A helper column can do that. With the values to check in A2:A100, enter =COUNTIF($A$2:A2,A2)>1 in B2 and fill it down. The first instance returns FALSE; a later occurrence returns TRUE. You can then filter column B for TRUE or use it to guide a review.

The expanding range is the key difference: its starting cell stays at A2, but its ending row advances as the formula is filled down. At each row, the formula counts appearances from the beginning of the list through that row. This makes it useful for distinguishing a first entry from repeats while preserving the original order.

For a readable label rather than TRUE or FALSE, with values in A2:A100, use =IF(COUNTIF($A$2:A2,A2)>1,"Repeat","First") in B2 and fill down. This formula labels each row, including a first appearance that has no later match. If you use formulas in a helper column, remember that sorting only one column can separate labels from the records they describe; sort the full table instead.

Check duplicates using multiple columns

When a record is defined by more than one field, use COUNTIFS. For example, suppose first names are in column A, last names in B, and dates in C, beginning in row 2. To flag rows where all three fields match another row, put =COUNTIFS($A$2:$A,A2,$B$2:$B,B2,$C$2:$C,C2)>1 in a helper column or use it as a custom conditional-formatting formula for the rows.

Each range-and-criterion pair adds a condition: the formula counts rows where the first name, last name, and date all match the current row. If your actual key is email address, use only the email column as the criterion. If it is product code plus location, use those two columns and omit unrelated fields. Using too many fields can miss duplicates that differ in a detail you do not consider important; using too few can flag valid records.

For a whole-row highlight, apply the rule to the table area, such as A2:C100, and keep the criteria references tied to the current row. For a helper column, put the formula beside the table and fill it down. Check a few known examples after setting up the rule so you can confirm that it matches your definition of a duplicate.

Find near-duplicates caused by spaces or case

Two entries can look identical but contain different underlying text. A trailing space, a nonprinting character, inconsistent capitalization, or a slightly different spelling can affect comparisons. Conversely, COUNTIF matching in Google Sheets is not case-sensitive, so entries such as Alex and ALEX are generally treated as the same match by that function.

For simple extra spaces and capitalization differences, make a normalized helper value. With the original text in A2, enter =LOWER(TRIM(A2)) in B2 and fill down. This trims ordinary extra spaces at the beginning and end, collapses repeated spaces between words, and converts the result to lowercase. You can then apply the duplicate check to column B. Keep the original column intact so you can review the source values before making changes.

Normalization is a choice, not a universal correction. Lowercasing is useful for many names and email addresses, but case can matter in some identifiers or source systems. TRIM also does not fix every possible hidden character or spelling variation. If a value still looks the same but does not match, inspect the source data and decide whether a cleanup step is appropriate rather than deleting records automatically.

Review a unique list or remove duplicate rows

If you want a separate list of distinct values without changing the source, use the UNIQUE function. With the entries in A2:A100, enter =UNIQUE(A2:A100) in an empty cell. Google Sheets returns the distinct entries in the order they first appear. The source range remains unchanged, and the result updates when the source data changes.

To remove duplicates from a table, first consider making a copy of the sheet or preserving the original data. Then select the table and choose Data > Data cleanup > Remove duplicates. In the dialog, confirm whether the range has a header row and select the columns that should determine which rows count as duplicates. Review those column choices carefully: selecting only an ID column can remove rows that share that ID even if other fields differ. Selecting every column instead treats rows as duplicates only when all selected values match.

Google Sheets removes rows from the selected data, so this is a change to the source rather than a visual flag. Check the resulting range and the removal summary before moving on. Undo may reverse a recent action, but it is safer to keep a copy when the data is important, particularly before cleanup on a large or shared sheet.

Choose the right method for the task

Use conditional formatting when you need to see repeated values while keeping all rows in place.
Use a helper formula when you need a TRUE/FALSE flag, a first-versus-repeat label, or a result you can filter.
Use COUNTIFS when duplicates are defined by a combination of columns rather than one field.
Use UNIQUE when you need a separate list of distinct values and want the source data left untouched.
Use Remove duplicates only when you are ready to delete matching rows from the selected range.

For a one-time check, conditional formatting is usually quick and reversible. For an ongoing import or list that changes frequently, a helper formula or UNIQUE result can be easier to maintain. For permanent cleanup, review the matching rule and keep a recoverable copy first. The best method depends on whether the goal is to find, label, display, or delete repeats.

Common problems and troubleshooting

If nothing is highlighted, check that the conditional-formatting range begins on the same row referenced by the formula. For example, a range starting at A2 should use A2 in its custom formula, not A1. Also verify that the range to count includes all relevant rows, and that the cells really contain equivalent values rather than values that only look alike.

If the header is highlighted, exclude it from the selected range and start the count range below it. If whole rows are unexpectedly highlighted, check that the formula uses the intended key column and that its column reference is locked with a dollar sign. A rule applied to a large range can also be misleading if the formula’s relative references were written for a different starting cell.

Some Google Sheets locales use semicolons instead of commas as formula argument separators. If Sheets rejects a formula with commas, check the spreadsheet’s locale settings and use the separator expected by that locale. Also make sure you chose Custom formula is in conditional formatting; typing a formula into a different rule type will not produce the same result.

How this differs from Excel

The core counting formulas are very similar in Google Sheets and Excel, but the menus are not identical. In Excel for Microsoft 365 on Windows, a common highlighting route is Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Google Sheets instead uses Format > Conditional formatting and can apply a custom formula. Excel’s removal command is under Data > Remove Duplicates; Google Sheets places it under Data > Data cleanup > Remove duplicates.

Excel and Sheets also differ in some available functions and interface details, so a formula or menu instruction written for one program should not automatically be assumed to apply to the other. The examples here use Google Sheets in a web browser. When following an Excel guide, check its version and platform before using its steps.

Frequently Asked Questions

Does Google Sheets have a built-in duplicate finder?
Yes. Use conditional formatting to highlight matching values, or choose Data > Data cleanup > Remove duplicates to delete repeated rows from a selected range. The first method preserves the data; the second changes it.

How do I highlight duplicates in one column?
Select the data cells, choose Format > Conditional formatting, set the rule to Custom formula is, and enter a COUNTIF rule that matches the selection’s starting row. For a range beginning at A2, use =COUNTIF($A$2:$A,A2)>1.

Can I find duplicates across two columns?
Yes. Use COUNTIFS with a range-and-criterion pair for each column in the key. For instance, to identify rows where both A and B match, count rows matching the current row’s A value and B value. Add more pairs if the record key uses more fields.

Does Remove duplicates delete the first copy too?
It removes repeated rows according to the columns you select and keeps a single instance of a matching record. Because the command edits the selected data, confirm the range, header setting, and comparison columns before proceeding.

Why does a duplicate check miss values that look the same?
The underlying contents may differ because of spaces, hidden characters, or spelling variations. Use a helper column to normalize simple differences, inspect the source values, and verify that your cleanup rule suits the data before removing anything.

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.