How to Remove Duplicates in Google Sheets: A Safe Guide

To remove duplicate rows in Google Sheets, select your data, then choose Data > Data cleanup > Remove duplicates. Confirm whether the range has a header row and which columns to compare, then select Remove duplicates; Sheets keeps one matching row and removes the others.

What the Remove Duplicates tool does

Google Sheets’ built-in cleanup tool checks rows in the range you select and removes rows it identifies as duplicates. You can compare every selected column or only chosen columns. The tool keeps one occurrence of each matching record, so it is useful for cleaning repeated customer entries, order lines, sign-ups, or imported lists.

A key distinction is that the tool removes duplicate rows, not just repeated text inside a cell. If you select a whole table, Sheets removes the repeated records across that table. If you select only a single column, it can remove repeated values from that column, but other columns outside the selection are not a safe way to preserve associated information. For a contact list, for example, select the name, email, and phone columns together if you want to remove repeated contacts as complete records.

It is also different from highlighting duplicates: removal changes the data, while highlighting only marks matching values so you can review them. If you are unsure which record should remain, inspect or highlight first rather than deleting immediately.

Prepare the range before cleaning

Before using the tool, decide what counts as a duplicate. Two rows might be identical across every field, or they might count as the same person if they share an email address even when their names or phone numbers differ. That decision determines which columns you select in the removal dialog.

Include all related columns. If a row is a single record, select the full table so that its fields stay together.
Exclude unrelated data. Do not include notes or totals outside the dataset just because they are nearby.
Check the boundaries. Make sure the selected range does not omit the last rows or include a different table below it.
Make a safety copy. Duplicate the sheet tab or use File > Make a copy before a cleanup that would be difficult to reconstruct.

For example, suppose columns A through D contain name, email, city, and signup date. If you want to remove only fully repeated records, compare all four columns. If email is the unique identifier and you want one row per email, compare the email column only—but understand that Sheets will keep one of the matching records and discard the other selected rows. Choose that rule deliberately when the other fields conflict.

Remove duplicates step by step

Select the data range, including all columns that belong to each record. You can click and drag across the cells, or click a cell in a contiguous table and select the table range.
Open Data > Data cleanup > Remove duplicates in the Google Sheets menu.
In the dialog, check Data has header row if the first row contains labels such as Name or Email. This tells Sheets not to treat those labels as ordinary records.
Review the columns listed in the dialog. Keep all relevant columns selected for full-row matching, or select only the fields that define a duplicate for your task.
Choose Remove duplicates. Review the confirmation message, which reports how many duplicate rows were removed and how many unique rows remain.

Afterward, scan the first and last few records and check a few known repeated values. If the result is not right, use Undo immediately—Ctrl+Z on Windows or Command+Z on a Mac—or restore the saved copy. Undo is most reliable before making additional edits.

Choose which columns define a duplicate

The column selection controls the meaning of “duplicate.” When all columns are selected, two records must match across the selected fields to count as duplicates. This is appropriate when you want to remove exact repeated rows but preserve records that differ in any meaningful field.

When only one or a few columns are selected, Sheets treats rows with matching values in those columns as duplicates, even if their unselected fields differ. Consider a table with two entries for alex@example.com, one with an old phone number and one with a new one. Matching on email alone removes one entry; it does not merge the phone numbers or decide which details are correct. Sort or review the records first if you need a particular version to remain.

The tool retains one matching occurrence, rather than combining information from duplicate rows. If the order matters—for example, you want the newest entry retained—sort the data into the order you want before removing duplicates. Keep the header row in place and sort the entire table, not just one column, so record fields do not become mismatched.

Use UNIQUE when you want a separate result

Removing duplicates edits the selected data. If you would rather keep the original intact and create a deduplicated list elsewhere, use the Google Sheets UNIQUE function. With records in A2:C100, enter =UNIQUE(A2:C100) in an empty cell. Sheets returns the distinct rows in a new output range, leaving the source data unchanged.

For a list of email addresses in A2:A100, enter =UNIQUE(A2:A100) in an empty cell to return one of each distinct value. Leave enough empty cells below and, for multi-column data, to the right: the results expand into those cells. If existing content blocks the output, Sheets reports an expansion error. The result is formula-driven, so changes in the source range can change the displayed list; copy and paste values if you need a fixed snapshot.

UNIQUE is useful for reports and review lists, but it does not clean the original range. It also does not determine which conflicting record is best; it returns distinct rows according to the values in the range. Use the removal tool when you intend to edit the source, and a formula when you want a non-destructive view.

Why apparent duplicates may remain

Rows that look the same on screen may contain different underlying values. Extra spaces, a nonbreaking space copied from a webpage, a different punctuation mark, or a hidden character can make two entries distinct. For instance, Jordan Lee and Jordan Lee may appear identical at a glance but differ by a trailing space. Differences in capitalization can also create uncertainty, so check the actual cell contents and test a small copy if the result depends on case.

Formatting alone is not a reliable way to identify a duplicate: two cells can look different because of number or date formatting while holding equivalent values, or look alike while storing different text. If the cleanup misses a suspected match, inspect the values, trim unwanted spaces, and standardize formats before trying again. For names and addresses, decide whether abbreviations, punctuation, and spelling variations should count as the same record; the tool cannot infer that business rule.

Another common cause is selecting too many or too few columns. If a timestamp is included, records with the same name and email but different timestamps may not be exact row duplicates. If only the email column is selected, separate people who share a household address are not affected by that choice, but two different records sharing an email may be treated as duplicates. Review the selection rather than assuming Sheets knows which fields matter.

Review, undo, and protect important data

Removal is a destructive cleanup of the selected range. Google Sheets generally lets you undo the operation, but do not rely on Undo as your only backup for valuable or shared data. Make a copy of the tab or spreadsheet first, especially if other people use it or if the source came from an import that would be hard to repeat. A copy also makes it easier to compare the original and cleaned versions.

For a large dataset, consider doing the first pass on a copy and comparing row counts or checking a few known records. If the output is wrong, undo at once or return to the original copy and revise the selected columns. On a shared sheet, tell collaborators before changing a source list: removing rows may affect formulas, charts, or references elsewhere in the workbook.

Google Sheets and Excel are similar, but not identical

In Google Sheets in a web browser, the built-in path is Data > Data cleanup > Remove duplicates. Excel for Microsoft 365 on Windows has a similar feature under Data > Remove Duplicates, but the menu location and dialog are not identical. Instructions written for Excel should not be followed as though they were Google Sheets steps. In either program, select the intended range and decide carefully which columns establish a duplicate before confirming removal.

Frequently Asked Questions

Does Google Sheets keep the first duplicate?
Sheets keeps one occurrence of each duplicate and removes the other matching rows. If you need a particular version to remain, arrange the records in the desired order first and sort the complete table, then run the cleanup.

Can I remove duplicates from just one column?
Yes. Select the column and use the same cleanup path, or use =UNIQUE(A2:A100) in an empty cell to make a separate list. If that column is part of a larger record, select the full record range when removing rows so associated values are handled together.

How do I remove duplicates but keep the original data?
Use =UNIQUE(A2:C100) in a blank area, adjusting the range to your table. It returns distinct rows without deleting the source. Make sure the output area has room for the results.

Why did Google Sheets not remove two values that look identical?
They may differ in spaces, hidden characters, punctuation, or underlying values. Inspect and standardize the cells before running the tool again. Also check whether the selected columns include fields, such as timestamps, that differ between the rows.

Can I undo Remove duplicates?
Usually, yes: press Ctrl+Z on Windows or Command+Z on Mac soon after the cleanup. For important data, make a copy first because later edits can make recovery less straightforward.

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.