Google Sheets Duplicate Values: Find and Remove Them

Yes, Google Sheets can find duplicate values, highlight them, and remove duplicate rows. In a browser, use Format > Conditional formatting to mark repeats, or Data > Data cleanup > Remove duplicates to delete them; make a copy first if you may need the original records.

What counts as a duplicate in Google Sheets?

A duplicate is a value or record that matches another according to the fields you choose to compare. A repeated email address in one column is a duplicate value; two rows with the same customer name and order number may be duplicate records even if other columns differ. Decide what “same” means for your task before cleaning anything.

For example, a list of event registrations might contain the same email twice because someone submitted the form again. If email is the unique identifier, matching email addresses are duplicates even when the names are typed differently. In an inventory sheet, however, two rows with the same product name might be valid if they refer to different locations or batch numbers. Comparing only one column could remove data you intended to keep.

Google Sheets generally treats capitalization as irrelevant in common duplicate checks. For instance, a repeated text value with different letter case can still match. Spaces and other invisible characters are a separate issue: “alex@example.com” and “alex@example.com ” may not behave like identical text. Formatting differences, such as color or bold type, do not make otherwise matching cell contents different for duplicate removal.

Highlight duplicates with conditional formatting

Highlighting is a useful first step because it lets you inspect the affected cells without changing or deleting data. In Google Sheets in a web browser:

Select the range to check, such as A2:A100. Exclude the heading if row 1 contains a column name.
Choose Format > Conditional formatting.
In the side panel, set “Format cells if” to Custom formula is.
With values in A2:A100, enter =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1).
Choose a formatting style, then select Done.

The formula checks whether the value in each row occurs more than once in the specified range. The dollar signs keep the checked range fixed as the formatting rule is applied down the column, while A2 adjusts for each row. The condition that A2 is not blank prevents empty cells from being highlighted as repeated values. Change the range references to fit your sheet.

To check a whole column, you can use a range such as A2:A rather than setting an end row. A bounded range can be easier to review and may be more efficient on a very large sheet. Conditional formatting marks every matching occurrence, including the first one; it does not tell you which copy to keep.

Use a helper column to identify later copies

If you want to flag only the second and subsequent appearances, add a helper column beside the data. With values in A2:A100 and a header in A1, put this formula in B2 and fill it down through B100:

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

The first appearance returns FALSE, while later appearances return TRUE. For example, if A2:A5 contains “Pear,” “Plum,” “Pear,” and “Pear,” the formula returns FALSE, FALSE, TRUE, TRUE. You can filter the helper column for TRUE to inspect the later copies. This method is convenient when you need to decide row by row what to retain instead of deleting all repeats automatically.

Because the range in COUNTIF grows from $A$2 to the current row, each formula counts how many times the value has appeared so far. Keep the starting row aligned with your first data row. If your data begins in row 5, adjust both the formula’s row references and the range accordingly.

Remove duplicate rows with the built-in tool

Google Sheets includes a removal tool for cleaning a selected data range. Select the table, including all columns that should stay together, and choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the selected data has a header row and choose which columns should be used to identify duplicates. Review those choices before confirming.

If you select the email column as the comparison field, rows with a repeated email can be treated as duplicates even when their other cells differ. If you select every column, the tool looks for rows that match across the selected columns; rows with the same email but different notes will not be identical across the full selection. The comparison columns define what counts as a duplicate, so selecting the wrong set can remove records you meant to preserve.

The cleanup tool removes duplicate rows from the selected data, rather than merely marking them. Keep an untouched copy of the source sheet or duplicate the tab before proceeding if the data matters. After removal, check the remaining row count and spot-check records, particularly if the sheet contains formulas, notes, or columns outside the selected range. The tool is for cleaning data, not for merging information from repeated records.

Check duplicates across multiple columns

Sometimes no single field identifies a record. A combination of columns may be the right test, such as matching both a first name and last name, or a product code and warehouse. For a helper-column check where names are in A2:A100 and locations are in B2:B100, enter the following in C2 and fill down:

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

This returns TRUE for a later row when the same name-and-location combination has already appeared. For example, “Mina Chen” at “East” and “Mina Chen” at “West” are distinct combinations, while a second “Mina Chen” at “East” is flagged. To ignore rows with missing comparison values, add a blank check appropriate to your data; whether a blank is valid or incomplete depends on the records you are cleaning.

You can also select multiple comparison columns in the Remove duplicates dialog. Make sure the selected columns represent the identity rule you intend to use. When deleting rows, select the entire table so that values in other columns remain attached to the correct record.

Return a separate list of unique values

If you want a clean output without changing the source, use the UNIQUE function in an empty area or on another tab. With source values in A2:A, enter:

=UNIQUE(A2:A)

The result is a list of distinct values from the source range, generally keeping the order in which they first appear. Leave enough empty cells below the formula for its results to expand. If other content blocks that output area, Sheets cannot display the full result until the obstruction is cleared. A unique list is useful for summaries or dropdown sources, but it does not delete repeated rows or combine details from them.

For unique records rather than unique entries in one column, give UNIQUE a range containing the record columns you want compared. The output then contains distinct rows based on the selected columns. As with the cleanup tool, choosing only some columns means differences in excluded columns will not affect whether two rows are considered the same in the output.

Clean spaces and other causes of unexpected results

A duplicate check can seem wrong when cells look alike but contain different underlying text. Leading or trailing spaces, line breaks, nonbreaking spaces copied from a website, and inconsistent punctuation can all affect matching. Conversely, case differences may be ignored by a duplicate check even if capitalization matters to your workflow. Inspect suspicious cells in the formula bar or test a small sample before applying cleanup to the full dataset.

For ordinary leading and trailing spaces, TRIM can help create a normalized comparison column. With source text in A2, enter =TRIM(A2) in B2 and fill down. This returns text with extra ordinary spaces removed while keeping single spaces between words. It does not remove every unusual whitespace character, and it changes the text representation, so compare the cleaned results before replacing source data.

Other common reasons for a surprising result include:

Headers included as data: mark the header-row option in the removal dialog or exclude the heading from your formula range.
Only part of the table selected: deleting duplicates from one column alone can leave associated values in other columns out of alignment. Select complete records when removing rows.
Blank cells: a repeated blank may be highlighted by a simple count rule. Add a nonblank condition if blanks should not count.
Different comparison rules: checking one identifier is not the same as checking every column in a row.
Formula results: cells containing formulas may display the same text while using different formulas. Decide whether you are comparing displayed values or preserving formula logic before cleanup.

Choose the safest method for the job

Use conditional formatting when you want a visual review and do not want to alter the sheet. Use a helper column when you need to isolate later occurrences or compare several fields with an explicit rule. Use Remove duplicates when you are confident about the comparison columns and want to delete repeated rows. Use UNIQUE when you need a separate distinct list while keeping the original data intact.

These methods answer slightly different questions. “Which values repeat?” calls for highlighting or a count-based helper column. “Which rows should be deleted?” calls for careful selection and removal. “What are the distinct categories?” is often best answered by UNIQUE. No method can decide which record is authoritative when duplicate rows contain conflicting information; that requires checking the source or applying a business rule.

Google Sheets is a browser-based spreadsheet, and the menu paths above refer to its web interface. Excel has similar ideas, but its menus and some formula behavior or available features can differ by version. If you are following instructions in Excel, use Excel-specific steps rather than assuming the Google Sheets menu labels are identical.

Frequently Asked Questions

Can Google Sheets highlight duplicates automatically?
Yes. Select the range, open Format > Conditional formatting, choose “Custom formula is,” and use a COUNTIF-based rule. The highlighting updates as values in the checked range change.

Does Remove duplicates keep the first occurrence?
The tool removes repeated rows from the selected data, leaving a distinct set. Make a copy first if you need to preserve every original row, and review the comparison columns and header setting before confirming.

Can I find duplicates across two columns?
Yes. Compare both fields with COUNTIFS in a helper column, or select both columns as the comparison fields in the Remove duplicates dialog. This lets you treat a pair of values as the record’s identity.

Why are similar-looking values not being detected?
They may contain extra spaces, line breaks, punctuation differences, or other hidden characters. They may also differ in a field included in the comparison. Inspect the underlying cell contents and test a normalized copy before removing data.

Can I remove duplicates without changing the original list?
Yes. Put a UNIQUE formula in a blank area or another tab to produce a separate distinct list. Keep the output area clear so the results can expand, and remember that this creates a list rather than deleting or merging source 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.