Google Sheets Duplicate Data: Find and Remove It

Yes. Google Sheets can find, highlight, and remove duplicate data: use Data > Data cleanup > Remove duplicates to delete repeated rows, or use conditional formatting to mark them without changing your data. The right method depends on whether you mean repeated values in one column or entire records that match across several columns.

What counts as duplicate data?

A duplicate can mean the same email address appears twice in a list, or that every field in two rows is the same. Those are different checks. If a customer name appears more than once because that customer placed multiple orders, removing repeated names could erase valid records. Before cleaning anything, decide which fields identify a duplicate for your purpose.

For example, a contact sheet might have columns for name, email, and city. If email uniquely identifies a person, two rows with the same email may be duplicates even if the city or spelling differs. If you are checking for copied records, you might instead require all three columns to match. Google Sheets lets you choose which columns the removal tool checks, so you can match the rule to the data rather than treating every repeated value as an error.

Remove duplicate rows with the built-in tool

In Google Sheets in a web browser, select the cells or table you want to clean, then choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the selected range has a header row and select the columns that should be compared. Click Remove duplicates to apply the change. Sheets reports how many duplicate rows it removed and how many unique rows remain.

Make a copy of the sheet or duplicate the tab if the original data matters.
Select the complete table, including every column you want to keep together.
Open Data > Data cleanup > Remove duplicates.
Set the header option correctly, then check only the columns that define a duplicate.
Review the removal summary and inspect the remaining rows.

When several selected columns match, Sheets treats those rows as duplicates and retains one copy. If you select only the email column, rows with repeated emails can be removed even when other fields differ; that can leave you with one version of a contact and discard information from another. Selecting the full table instead removes only rows that match across the selected fields. Choosing a header row matters because otherwise the column labels can be treated as ordinary data.

Highlight duplicates before deleting anything

Conditional formatting is a safer first step when you want to inspect matches. For a list of values in column A with a header in row 1, select A2:A and choose Format > Conditional formatting. Under Format cells if, choose Custom formula is, enter the formula below, and choose a highlight style. The rule marks every nonblank value that occurs more than once, including the first occurrence.

With the values to check in A2:A, use this conditional-formatting formula:

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

The dollar signs keep the lookup range anchored as the rule evaluates each row, while A2 changes to A3, A4, and so on. The blank check prevents empty cells from being highlighted as repeated entries. To check only a finite list, such as A2:A500, you can use that bounded range in both the selection and formula.

To highlight rows that repeat across three columns, select A2:C and set a custom formula. With the table in A:C and headers in row 1, use:

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

This checks whether the combination of values in A, B, and C appears more than once, and avoids marking wholly empty rows. Adjust the formula if your table has a different number of columns. A highlighted match is a prompt to review the record, not proof that it should be deleted.

Use a formula to create a unique list

If you need a clean output while preserving the original table, the Google Sheets UNIQUE function can return distinct rows in a separate area. For example, if a dataset occupies A2:C100 and its headers are in row 1, enter this formula in an empty cell outside the source data:

=UNIQUE(A2:C100)

Sheets returns the first occurrence of each distinct row and leaves later identical rows out of the result. The formula output updates when the source range changes, but it does not delete or edit the source records. Leave room for the result to expand; cells in its output area can prevent the array from displaying. This is useful for a report or a review list, but it is not a substitute for deciding what counts as the same record.

Why apparent duplicates may not match

Data that looks alike on screen can contain differences that affect cleanup. A name may have an extra space, a number may be stored as text in one row and as a number in another, or a date may include a time component that is not obvious in the displayed format. Conversely, a visual difference such as capitalization may not be treated as a meaningful distinction by a particular comparison method. Do not assume that appearance alone tells you how two cell values will be compared.

Spaces and hidden characters: copied data can include leading spaces or nonprinting characters. Clean or standardize values in a helper column, then review the result.
Dates and numbers: confirm that values use consistent types and formats. A displayed date can hide a time value.
Different definitions: decide whether matching one field, a combination of fields, or the entire row establishes a duplicate.
Repeated blanks: empty rows are usually not meaningful records. Exclude them from checks or remove them separately after confirming they contain no needed information.

For a simple cleanup helper, if the original text is in A2, enter =TRIM(A2) in B2 and fill it down. This removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words. Compare or deduplicate the cleaned helper values only after checking that this normalization is appropriate; do not overwrite source values automatically when exact original text matters.

Prevent duplicates at the point of entry

Removing duplicates is retrospective: it finds repeats after they have entered the sheet. If a column should contain unique identifiers, make that rule visible to people entering data. You can use conditional formatting to flag a value as soon as it repeats, add a note explaining the expected format, or create a controlled input process that checks existing records before adding a new one. Google Sheets does not make every arbitrary column unique automatically just because it is used as an identifier.

A practical workflow for a shared contact list is to identify email as the candidate key, flag repeated emails with conditional formatting, and review each flagged pair before cleanup. A shared address, a changed email, or a household using one address may be legitimate, so a match still needs context. For order or transaction data, a repeated customer ID is usually expected; a better duplicate check might combine the order ID with another field that defines a unique transaction.

Safe cleanup and recovery

Removing duplicates changes the selected data, so keep an untouched copy when the source is important or when the matching rule is uncertain. You can duplicate a sheet tab before cleanup using the tab menu, or copy the spreadsheet. After removal, check the row count, inspect examples of removed records if available in your backup, and verify that totals or downstream reports still make sense.

Undo can reverse a recent action in many ordinary editing situations, but it is not a dependable backup strategy, especially after other edits or when collaborators are working in the file. For sensitive, regulated, or operational records, follow the organization’s retention and review process rather than deleting rows solely because a tool flags them. This advice is about data handling, not a legal or compliance determination.

Google Sheets and Excel are not the same interface

The steps here are for Google Sheets in a web browser. Excel for Microsoft 365 has its own Remove Duplicates command on the Data tab, but its dialog and surrounding menus differ; do not follow an Excel ribbon path while looking for the Sheets command. Both programs can identify repeated records, but formulas, formatting controls, and collaboration behavior are not identical. If a workbook is being moved between them, check that formulas and cleanup results still behave as intended after conversion.

Frequently Asked Questions

How do I find duplicates in Google Sheets without deleting them?

Select the cells, choose Format > Conditional formatting, and use a custom formula such as =AND(A2<>"",COUNTIF($A$2:$A,A2)>1) for values in column A. The rule highlights matches while leaving the data in place.

Can Google Sheets remove duplicates based on just one column?

Yes. Open Data > Data cleanup > Remove duplicates and select only the column that defines a match. Be aware that removing rows based on one field can discard other information from rows whose remaining columns differ.

Does Remove duplicates delete the first copy too?

The cleanup process keeps one occurrence of a matching record and removes later repeated rows within the selected range. Make a copy first if you need to compare the original and cleaned data or if the correct row to retain is uncertain.

Why are duplicates not being highlighted?

Check that the conditional-formatting rule starts on the same row as its formula reference, that its applied range covers the data, and that the formula points to the intended column. Also look for spaces, hidden characters, mixed data types, or other differences between cells that appear identical.

Does UNIQUE delete duplicate rows from the original data?

No. UNIQUE displays a distinct result in the formula’s output area and leaves the source range unchanged. Use the built-in removal tool only when you intend to change the selected records.

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.