Google Sheets Identify Duplicates: A Practical Guide

Yes. Google Sheets can identify duplicate values with conditional formatting or a COUNTIF formula, and it can remove duplicates with Data > Data cleanup > Remove duplicates. Use highlighting or a formula when you want to review matches; use the removal tool only when you are ready to delete repeated rows.

Choose how to find duplicates

The best method depends on what you mean by a duplicate and what you want to do next. A duplicate might be a repeated email address in one column, a repeated combination of name and date, or a completely repeated row. Sheets does not decide which definition is right for your data, so set that rule before marking or deleting anything.

Conditional formatting: highlights matching cells while leaving the data unchanged. It is useful for a quick visual review and updates as values change.
A formula: can count occurrences, label repeated entries, or identify only later occurrences. It is useful when you want a visible result column or need to filter matches.
Remove duplicates: deletes repeated rows according to the columns you select. It is appropriate after you have checked the data and made a copy if needed.

These are Google Sheets instructions for the web browser. Excel has similar conditional-formatting and duplicate-removal features, but its menus and some formula details differ; the menu paths below are for Sheets, not Excel.

Highlight repeated values with conditional formatting

To highlight repeated entries in a single column, select the cells you want to check, such as A2:A1000, then choose Format > Conditional formatting. In the conditional-formatting panel, set the rule to Custom formula is and enter the formula below. Choose a formatting style and click Done.

With values in A2:A1000 and the rule applied to A2:A1000, use =AND(A2<>"",COUNTIF($A$2:$A,A2)>1). This highlights every nonblank cell whose value appears more than once in column A. The dollar signs keep the lookup range anchored while the row reference A2 adjusts for each cell being formatted.

The blank check matters: without it, blank cells may be treated as repeated values and highlighted. To highlight only the second and later occurrences, rather than every member of a repeated group, use a running count instead.

With values in A2:A1000 and the rule applied to A2:A1000, use =AND(A2<>"",COUNTIF($A$2:A2,A2)>1). The first occurrence is not highlighted; a later occurrence is highlighted once its running count exceeds one. For example, if A2:A5 contains blue, red, blue, blue, the third and fourth cells are marked.

Use COUNTIF to label or count duplicates

Conditional formatting changes appearance, not the cell’s contents. If you want a result you can sort, filter, or use in another calculation, put a formula in a helper column. For example, label each repeated email as “Duplicate” and each first or unique email as “First or unique.”

With email addresses in A2:A1000 and the label formula starting in B2, enter =IF(A2="","",IF(COUNTIF($A$2:$A$1000,A2)>1,"Duplicate","First or unique")) in B2 and fill it down. For an email appearing three times, all three corresponding rows receive the Duplicate label. COUNTIF counts how often the current value occurs in the chosen range; the IF statements leave blank source cells blank and translate the count into readable text.

If you need the number of occurrences rather than a label, use a count formula. With values in A2:A1000 and the result formula in B2, enter =IF(A2="","",COUNTIF($A$2:$A$1000,A2)) and fill down. Each matching cell will show the group’s total—for instance, 3 for each of three identical entries. To display only repeated values, filter the result column for numbers greater than 1.

Check duplicates based on multiple columns

Sometimes a value is only a duplicate when two or more fields match together. A surname may occur many times, for example, but the combination of first name and surname may be the meaningful test. COUNTIFS checks several conditions in the same row and is a practical way to test such combinations.

With first names in A2:A1000 and surnames in B2:B1000, apply conditional formatting to A2:B1000 and use =AND(OR($A2<>"",$B2<>""),COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1). It highlights both cells in each nonempty row when that first-name-and-surname combination occurs more than once. The OR check avoids marking a completely blank row, while still allowing a partly blank record to be checked.

To identify exact repeated rows, include every column that defines the record in the test. For a table with first name, surname, and date in A:C, add a matching condition for column C to COUNTIFS. If you leave out a column, rows that differ only in that omitted field can be counted as duplicates. Conversely, including an irrelevant column can prevent otherwise repeated records from matching.

Clean data before comparing it

Two entries can look the same to a person but fail to match as expected because one contains extra spaces or inconsistent formatting. For instance, “Ava Lee” and “Ava Lee ” have a trailing space in the second value. Imported data may also use different capitalization, punctuation, date formats, or hidden characters. A duplicate check reflects the values Sheets evaluates, not necessarily the meaning you intend.

Use Data > Data cleanup > Trim whitespace to remove extra spaces from selected data. Review the affected range first, especially if spaces are meaningful in your dataset.
For case and surrounding-space normalization, create a helper column. With source text in A2, enter =LOWER(TRIM(A2)) in B2 and fill down, then check column B for repeated normalized values.
Make dates consistent before comparing them. Values that look alike may be stored as different text or date values, particularly after importing files.
Check whether punctuation or identifiers should be standardized. For example, “555-0100” and “5550100” are different strings unless you deliberately normalize them.

COUNTIF is generally case-insensitive, so entries such as “Ava” and “AVA” are normally counted together. It also interprets asterisks and question marks in criteria as wildcard characters. If a dataset contains those characters as literal parts of values, check the results carefully rather than assuming every apparent match is an exact character-for-character match.

Remove duplicates without losing the wrong data

When you have reviewed the results and want to delete repeated rows, select the relevant table and choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the range has a header row, then choose which columns define a duplicate and confirm the action. Selecting every column checks for rows that match across the whole table; selecting only an ID column removes rows that share that ID even if other fields differ.

The removal tool keeps one instance of a repeated record and removes the other matching rows. It is not the same as highlighting: removal changes the dataset, so formulas or reports that refer to deleted rows may change too. Make a copy of the sheet or duplicate the tab before a large cleanup, and verify the selected range and columns in the dialog. If you remove something by mistake, Undo may restore it immediately, but a backup is more dependable than relying on undo history.

After removal, check the resulting row count and spot-check representative records. A duplicate ID may indicate a genuine data-entry error, but it may also be valid if one person can have several transactions or one product can appear on multiple order lines. Removing repeated keys without considering what each row represents can erase legitimate records.

Common problems and how to troubleshoot them

If a rule highlights too many cells, check whether blank cells are included and whether the selected apply-to range begins on the same row used in the formula. A custom conditional-formatting formula is evaluated relative to the top-left cell of its applied range. For a range starting at A2, a formula referring to A2 is aligned; if the range starts at A1 but the formula assumes A2, the rule can appear shifted.

If it misses values that look identical, inspect spaces, punctuation, data types, and imported formatting. A number stored as text may not behave like a numeric value. Also check whether the formula’s range includes all the records: a formula that ends at row 100 may overlook new entries below that row. An open-ended range such as $A$2:$A can accommodate added rows, although very large sheets may calculate more efficiently with a range sized to the data.

For formulas filled down a helper column, confirm that the data range is fixed with dollar signs but the current-row reference is not. In =COUNTIF($A$2:$A$1000,A2), the source range remains the same as the formula is copied, while A2 becomes A3, A4, and so on. If both references are fixed, every row tests the same value.

Which method should you use?

Use conditional formatting when the goal is to see repeated values in place and decide what to do yourself. Use a helper formula when you need counts, labels, filters, or a repeatable check that other calculations can use. Use Remove duplicates only when you intend to delete rows and have defined exactly which columns make two records duplicates.

For a one-time cleanup, a sensible sequence is to make a copy, normalize obvious formatting issues, highlight or label matches, inspect the records, and only then remove duplicates if appropriate. For a list that changes regularly, keep a formula or conditional-formatting rule in place so new repeated entries are easier to spot. Neither method knows which record is more trustworthy; that decision may require checking dates, source systems, or other fields.

Frequently Asked Questions

Can Google Sheets automatically highlight duplicates?
Yes. Select the range, choose Format > Conditional formatting, select Custom formula is, and use a COUNTIF rule such as =AND(A2<>"",COUNTIF($A$2:$A,A2)>1) for a range beginning at A2. The rule highlights matching nonblank values without deleting them.

How do I find duplicates in two columns in Google Sheets?
Use COUNTIFS to test both columns together. For first names in A and surnames in B, a rule based on =COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1 identifies repeated combinations. Add a blank-row check if empty records should not count.

Does Remove duplicates delete the first copy too?
The tool removes repeated rows while retaining one instance of each group, rather than deleting every member of a duplicate group. Which rows qualify depends on the columns selected in the Remove duplicates dialog.

Can I find duplicates without deleting anything?
Yes. Conditional formatting and COUNTIF formulas only highlight or report matches. They leave the original records in place, so they are safer choices when you still need to review the data.

Why does Sheets say two different-looking values are duplicates?
COUNTIF is generally not case-sensitive, so different capitalization may still match. Extra spaces, wildcard characters, text-versus-number differences, and the columns included in the rule can also affect results. Normalize the data and inspect the rule before removing 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.