Finding Duplicates in Google Sheets: Practical Methods

To find repeated values in Google Sheets, select the cells, choose Format > Conditional formatting, and use a custom formula such as =AND(A2<>"",COUNTIF($A$2:$A,A2)>1) for a list in column A. To delete duplicates instead, select the data and choose Data > Data cleanup > Remove duplicates; review the selected columns before confirming.

Choose Between Highlighting, Flagging, and Removing

“Finding duplicates” can mean several different tasks. You might want to spot repeated entries without changing the sheet, create a list of which entries repeat, identify duplicate records across multiple columns, or permanently remove extra rows. These goals call for different methods.

Highlighting is useful for reviewing a list while keeping every row in place. Conditional formatting updates as values change.
Flagging with a formula adds a visible label or count beside each entry, which can be filtered or sorted.
Removing duplicates cleans the selected data, but changes it. Make a copy first if you may need the original rows.
Creating a separate list is useful for reporting or checking repeated values without altering the source.

Before choosing, decide what counts as a duplicate. A repeated email address may be a duplicate even if the person’s name differs, while a repeated full row may be legitimate if the same transaction occurred twice.

Highlight Repeated Values with Conditional Formatting

For a single column, select the cells to check, such as A2:A, then open Format > Conditional formatting. Under “Format rules,” choose “Custom formula is,” enter the formula below, choose a formatting style, and click Done. The formula assumes the first data cell is A2; adjust the references if your list begins elsewhere.

With values in A2:A and the conditional-formatting range also set to A2:A, use =AND(A2<>"",COUNTIF($A$2:$A,A2)>1). The row reference A2 changes for each cell in the selected range, while $A$2:$A keeps the comparison range anchored. The AND condition prevents empty cells from being colored as duplicates.

This rule highlights every occurrence of a repeated value, including the first one. If “Maple Street” appears three times, all three cells are highlighted. That is often the clearest choice when you want to inspect the complete set of matching entries. If you want to mark only later occurrences, use a running count instead, as explained below.

Use a Formula to Flag Duplicates

A helper column is handy when you want a filterable result rather than colored cells. Add a heading such as “Status” in B1. With the values to check in A2:A and the formula entered in B2, use =IF(A2="","",IF(COUNTIF($A$2:$A,A2)>1,"Duplicate","Unique")), then fill it down. Each nonblank value is labeled “Duplicate” if it occurs more than once, or “Unique” otherwise.

To flag only the second and later occurrence, with the same list in A2:A and the formula in B2, use =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Repeat","First occurrence")) and fill down. The counted range grows as the formula is copied: the first instance has a count of one, while later instances have a count greater than one. You can then use the filter controls to show just rows labeled “Repeat.”

These formulas compare the value in each row with the list in column A. They do not delete or rearrange data. For large sheets, restrict the comparison range to the rows you actually use—for example, A2:A5000—rather than searching an unnecessarily large area.

Check Duplicate Records Across Several Columns

Sometimes a duplicate is defined by a combination of fields. For example, two rows might count as the same customer record only when the first name, last name, and email all match. Comparing just one column could incorrectly flag people who share a name or an email address that has been entered for a household.

With first names in A, last names in B, emails in C, and records starting in row 2, apply conditional formatting to A2:C. For the custom formula, use =AND(COUNTA($A2:$C2)>0,COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2,$C$2:$C,$C2)>1). The row is highlighted when the combination of all three fields occurs more than once; the COUNTA check avoids highlighting entirely blank rows.

For this method, include all fields that define identity and apply the rule to the complete row range you want reviewed. If an ID column should determine uniqueness by itself, use that column as the comparison instead. Agreeing on the rule first prevents the common mistake of treating records as duplicates merely because one visible field matches.

Find Repeated Values Without Highlighting the Source

Google Sheets’ UNIQUE function returns distinct entries, so it is useful for making a de-duplicated list, but it does not by itself give you a list of values that repeat. To return each repeated nonblank value once, with source values in A2:A and the result formula placed in an empty cell, use =UNIQUE(FILTER(A2:A,COUNTIF(A2:A,A2:A)>1,A2:A<>"")).

FILTER keeps values whose count is greater than one, and UNIQUE collapses repeated results into one result per value. The output is a separate, formula-generated list; the original cells remain unchanged. Keep the cells below the formula clear so the results can expand. If nothing matches, FILTER can return an error because it has no results; that does not mean the source list has been altered.

For a simple list of distinct values regardless of whether they repeat, with the source in A2:A and the formula in an empty cell, use =UNIQUE(A2:A). That answers a different question: it lists each value once, including values that appeared only once. In the UNIQUE function, the optional “exactly once” setting can be used to return values that occur exactly one time—not values that are duplicates.

Remove Duplicates Carefully

When you have decided to delete repeated records, select the full data range, including the columns that must stay together, then choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the selection has a header row and choose the columns that define a duplicate. Confirm the selection before running the cleanup.

If the full row should be considered, include every relevant column in the duplicate check. If only email address determines uniqueness, select that field as the comparison column—but make sure the selected range includes the associated rows and data you intend to clean. Checking just one column in a multi-column selection can remove a row whose other details differ. Keep a backup or duplicate the sheet before cleanup when the records matter.

Removing duplicates is not the same as highlighting them: it changes the data, and a cleanup may remove rows you later wish you had retained. For a reversible review, highlight or flag first, inspect the matches, and then decide whether deletion is appropriate.

Handle Spaces, Capitalization, and Other False Matches

Two entries can look the same but differ because one contains a leading space, an extra space, a line break, or a nonbreaking space copied from a web page. Conversely, COUNTIF comparisons in Google Sheets are generally not case-sensitive, so entries such as “North” and “north” are treated as matching by this approach. Decide whether capitalization or spacing should matter for your particular data.

A helper column can normalize common differences before checking. With the original text in A2 and the cleanup formula entered in B2, use =LOWER(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))) and fill it down. This converts text to lowercase, trims ordinary extra spaces, removes certain nonprinting characters, and replaces the nonbreaking-space character with an ordinary space. Run duplicate checks against the helper values if that normalized definition is appropriate.

Normalization is not a universal fix. CLEAN does not remove every possible unusual character, and TRIM does not make all visually similar characters identical. Also check whether numbers stored as text, dates with different underlying values, or punctuation differences are creating confusing results. Keep the original columns so you can compare cleaned values with the source before making a permanent change.

Common Problems and Practical Checks

Nothing is highlighted: Check that the conditional-formatting range starts on the same row referenced in the formula. A rule written for A2 should normally be applied beginning at A2, not A1.
Blank cells are highlighted: Add a nonblank condition such as AND(A2<>"",…) to the custom formula, and make sure the comparison range does not accidentally include unrelated blank data.
Different records are marked: Check whether the rule compares too few columns. A name alone is rarely a reliable unique identifier.
Matching entries are missed: Inspect spaces, hidden characters, spelling variations, and whether values are stored as text or numbers. Normalize or correct the data before relying on a result.
A formula produces an error: Make sure its ranges cover the same number of rows where required and that the formula is entered in the correct cell. In some spreadsheet locales, function arguments use semicolons instead of commas.

Google Sheets formulas and formatting update when the underlying values change, but a static cleanup does not automatically prevent someone from entering the same value again. If future duplicate entries must be discouraged, use data validation or a review process appropriate to the sheet; do not assume a one-time cleanup enforces uniqueness forever.

How Google Sheets Differs from Excel

The approaches are similar in Excel, but the interface paths differ. Google Sheets uses Format > Conditional formatting and Data > Data cleanup > Remove duplicates in a web browser. In Excel for Microsoft 365 on Windows, conditional formatting is under Home > Conditional Formatting, and duplicate removal is under Data > Remove Duplicates. Formula functions such as COUNTIF and COUNTIFS exist in both programs, though details of menus and some behavior are not identical.

Frequently Asked Questions

Does Google Sheets have a built-in way to highlight duplicates?
Yes. Select the target range, open Format > Conditional formatting, and create a rule with “Custom formula is.” A COUNTIF-based rule can highlight values that occur more than once without deleting them.

How do I highlight only the second copy of a value?
Use a running COUNTIF range such as =COUNTIF($A$2:A2,A2)>1 for values in A2:A, with the rule applied starting at A2. The first occurrence is not highlighted; later occurrences are.

Does UNIQUE show duplicate values?
Not by default. UNIQUE returns distinct values once. To list values that repeat, combine FILTER with COUNTIF and then use UNIQUE to show each repeated value once.

Can I find duplicate rows based on only one column?
Yes. In Remove duplicates, choose the column that defines a match while selecting the data range whose rows should be cleaned. Before confirming, check that the range and comparison columns reflect the records you intend to keep or remove.

Are duplicate checks case-sensitive?
COUNTIF-based checks in Sheets are generally case-insensitive, so different capitalization alone will not distinguish two values. If capitalization must be treated as meaningful, use a case-sensitive comparison approach rather than relying on COUNTIF.

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.