Remove Duplicates in Google Sheets: Steps and Options

To remove duplicates in Google Sheets, select the data range, choose Data > Data cleanup > Remove duplicates, indicate whether the range has a header row, choose which columns to check, and click Remove duplicates. Make a copy first if the original records matter: the command deletes rows from the selected range rather than merely hiding repeated values.

What Google Sheets considers a duplicate

The cleanup tool compares records using the columns you select. If you select every column in a table, it removes rows whose values match across all those columns. If you select only a key column, such as email address, it can treat rows with the same email as duplicates even when other columns differ. In that case, the tool removes an entire row, not just the repeated value in the key column.

For example, a contact list might contain two rows with the email address alex@example.com but different phone numbers. Selecting the whole table checks whether the complete rows match; selecting only the email column makes the email the basis for deduplication. The second choice can discard information in the other row, so decide which record should survive before using it.

Duplicates can also be less obvious than identical-looking text. Extra spaces, different date or number formats, formulas, and capitalization can affect how values appear or compare. If exact distinctions matter, check the result on a copy of the data before applying cleanup to the original.

Prepare the range before cleaning

First identify the complete table, including every column that belongs to each record. A common mistake is selecting just one column in a multi-column table and then removing duplicates: the result may not preserve the relationship between each name, date, and other field as intended. Conversely, selecting unrelated notes or unique timestamps can make otherwise repeated records look different.

Keep one header row at the top and make sure each column has a meaningful label.
Include all fields that should stay together when a row is removed.
Decide whether duplicates mean identical complete rows or matching values in specific key columns.
Make a copy of the sheet, or duplicate the tab, if you may need to restore deleted records.

For a one-time check, you can select the table by dragging over its cells or click a cell inside a contiguous data region. Check the highlighted range before proceeding; blank rows or adjacent data can make the selection broader or narrower than expected.

Remove duplicate rows with the menu

In Google Sheets in a web browser, use this sequence:

Select the range containing the records. Include the header if there is one.
Open Data > Data cleanup > Remove duplicates.
In the dialog, check Data has header row if the first selected row contains column names.
Choose the columns Sheets should use to identify duplicates. Select all columns for identical-row matching, or only the relevant key columns for matching by those fields.
Click Remove duplicates. Sheets reports how many duplicate rows it removed and how many unique rows remain.

Review the selected columns in the dialog before confirming. If you choose a key such as customer ID, Sheets can remove a later row with that ID even if its address or status differs. The first occurrence is generally the one retained, so sort the table beforehand if you want a particular record—such as the newest or most complete one—to appear first. Sorting is a deliberate choice: do not assume the cleanup tool will decide which version is best.

Choose between exact-row and key-column matching

Exact-row matching is appropriate when you want to delete only records that repeat every selected field. It is useful for imported lists that contain the same complete transaction more than once. Key-column matching is useful when a field should be unique, for example an employee number or email address, but it carries more risk because other differences in the row are not used to decide whether a record is a duplicate.

Consider an order table with order ID, product, and quantity. If two rows have the same order ID but different products, treating order ID alone as the key would remove one row, even though the records may describe separate line items. A suitable key might instead be the combination of order ID and product. Think of the chosen columns as the definition of “same record,” not simply as a shortcut to a shorter list.

If records that share a key need to be reviewed rather than automatically reduced to one, do not run Remove duplicates yet. Filter or sort the data to compare those records and decide which one to keep. The cleanup command is designed to remove matching rows, not reconcile conflicting information.

Use UNIQUE to show a clean list without deleting data

When you want a separate deduplicated result and want to leave the source intact, Google Sheets’ UNIQUE function is an alternative. It returns distinct rows from a range and updates when the source changes. Put the formula in an empty area with enough room for its results; existing content in the output area can prevent the results from expanding.

With a header in row 1 and records in columns A through C from row 2 onward, enter this formula in an empty cell:

=UNIQUE(A2:C)

The result contains one copy of each distinct row across columns A, B, and C, with the first-seen order preserved. This creates a result, not a destructive cleanup of the source. Copy the result and use Edit > Paste special > Values only if you need a static copy; check the menu options available in your Sheets interface before replacing source data.

UNIQUE compares the range you give it. It does not automatically keep every column from the original while deduplicating by just one of them. If you need to keep a full record based on a single key, decide which matching record should be retained and use a method that explicitly handles that requirement, or use the menu on a copy after arranging the preferred record first.

Flag repeats before removing them

Sometimes the safer first step is to highlight repeated values, inspect them, and only then delete anything. For a single key column, conditional formatting can flag values that occur more than once. Suppose the values to check are in A2:A. Select that range, choose Format > Conditional formatting, set the rule to Custom formula is, and enter the formula below. Choose a highlight style and click Done.

With values in A2:A and the conditional-formatting rule applied starting at A2, use:

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

The formula counts occurrences of each cell’s value in column A and highlights entries with a count greater than one. It flags all instances of a repeated value, including the first one, so it is a review aid rather than a command to keep one record automatically. For duplicates defined by several columns, a single-column rule is not enough; use the cleanup dialog’s selected columns or build a carefully checked rule that represents the full key.

Common problems and how to avoid them

The wrong number of rows disappears: Reopen the workflow on a copy and check the selected range, header setting, and comparison columns. A wrong header setting can cause the first data row to be treated as a heading or a heading to be treated as data.
Rows that look alike are not removed: Inspect the underlying values for spaces, different punctuation, or other inconsistencies. A cell that looks blank may contain a space, and values that display similarly can have different stored contents or formats.
Different records are treated as duplicates: You may have selected too few comparison columns. Include the fields that distinguish legitimate records, or review matches manually before deleting.
A useful row was kept instead of the preferred one: Sort the records so the version you want to retain comes first, then clean a copy and verify the outcome. Sorting can change row order, so preserve the original if order matters.
The formula result will not expand: Clear the cells below and beside the UNIQUE formula where its output needs to appear. The source range and output area should not overlap in a way that blocks the result.

Undo can sometimes restore a recent change, but it is not a substitute for a backup—especially if more edits have followed the cleanup. For important or shared data, duplicate the tab first and label the copy clearly so collaborators know which version is the working one.

How the Sheets method differs from Excel

These instructions use Google Sheets in a browser; the menu path is not an Excel path. Excel for Microsoft 365 has its own Data tab and a Remove Duplicates command, with a dialog for selecting columns. Both products can remove records based on selected fields, but their menus and dialogs are different. If you are following a guide written for Excel, do not assume its button names or exact steps apply in Sheets.

Sheets also supports formulas such as UNIQUE for producing a separate list, while the menu command is the direct option when you intend to remove rows from the selected data. Choose based on whether you need a permanent edit, a live derived list, or a review before deletion.

Frequently Asked Questions

Does Remove duplicates delete the entire row?
Yes. It removes duplicate rows from the selected range according to the columns you chose. If you select only a key column, a matching row can be removed even when its other fields differ, so include the relevant table and choose the comparison fields carefully.

How do I remove duplicates but keep the original data?
Use UNIQUE in an empty area to produce a separate list of distinct rows, or duplicate the sheet tab before running the cleanup command. The formula-based output can change as its source changes; copy and paste values if you need a fixed result.

Can I remove duplicates based on one column?
Yes. In the Remove duplicates dialog, select the key column as the comparison field. Remember that Sheets removes a whole row when that key repeats, so differences in other columns will not protect the row from removal.

Does Google Sheets keep the first duplicate?
The first occurrence is generally retained and later matching rows are removed. If a particular version should survive, put it first by sorting or otherwise arranging the data, then verify the result on a copy.

Why does UNIQUE still show rows that look duplicated?
UNIQUE compares the values across the range supplied to it. Rows may differ in a field that is easy to overlook, or contain spaces or other differences that are not obvious visually. Check all included columns and inspect the underlying cell contents.

Choose a reversible method when the data matters

For straightforward exact repeats, the menu command is the quickest way to clean a selected table. For a live, non-destructive list, use UNIQUE; for uncertain matches, highlight or inspect them before removing anything. The key decision is what counts as the same record and which version should remain. Defining that rule first—and keeping a copy when records are important—makes deduplication much less likely to erase useful information.

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.