Removing Duplicates in Google Sheets: A Practical Guide

To remove duplicate rows in Google Sheets, select your table, choose Data > Data cleanup > Remove duplicates, confirm whether the first row is a header, select the columns that define a duplicate, and click Remove duplicates. Sheets keeps the first matching row and removes later matches, so make a copy first if you might need the original data.

What counts as a duplicate?

A duplicate is not always an identical-looking row. It depends on which fields you use to decide that two records represent the same thing. For a contact list, you might define a duplicate by email address even if the names or phone numbers differ. For a sales table, you might compare the order ID, or a combination of customer ID and purchase date.

Google Sheets lets you choose the columns used for the comparison. If you select every column, rows are considered duplicates only when their values match across all those columns. If you select just the email column, records with the same email can count as duplicates even when other cells differ. That choice determines which information is kept, so decide what “same record” means before removing anything.

Also distinguish duplicate records from repeated values. A customer may legitimately appear on several order rows, for example. Removing those rows because the customer name repeats would discard valid transactions. Use a field or set of fields that uniquely identifies the kind of record you are cleaning.

Make a safe copy before cleaning

Removing duplicates changes the data rather than just hiding repeated entries. Before you proceed, consider duplicating the sheet tab or copying the table to a separate sheet. To duplicate a tab, use its tab menu and choose Duplicate. A copy is especially useful when the dataset contains formulas, manually entered notes, or information that would be difficult to reconstruct.

Check that the selected range includes the full table, including columns that belong to each record. If you clean only part of a table, related values in other columns may not stay associated with the correct records. Include headers when convenient, then indicate that the first row is a header in the removal dialog. If the sheet contains separate tables or unrelated notes, select only the intended table instead of the entire sheet.

Confirm that each row represents one record and that the table has a clear header row.
Decide which column or columns should define a duplicate.
Keep an untouched copy if there is any chance you will need to restore removed data.

Use the built-in Remove duplicates tool

In Google Sheets in a web browser, the built-in tool is usually the quickest option for permanently cleaning a table:

Select the full data range you want to clean. Include all columns that should remain together for each record.
Open Data > Data cleanup > Remove duplicates.
In the dialog, check Data has header row if the first selected row contains column names.
Under the columns list, select the columns Sheets should compare. Select all columns for exact row matches, or only the identifying fields for a narrower match.
Click Remove duplicates. Sheets reports how many duplicate rows were removed and how many unique rows remain.

Review the result rather than assuming the number removed is correct. Check a few records that you expected to be duplicates and a few that should have remained separate. If you removed something by mistake, use Edit > Undo promptly, or restore the saved copy. Undo may no longer be available after other changes or after leaving the editing session.

Choose the columns that define a match

The most important setting is the list of columns to analyze. Suppose a table has Name, Email, and Department. If you select all three, two rows with the same email but different department values will remain. If email is the reliable identifier and the goal is one row per address, select only Email. The tool then treats repeated email values as matches, regardless of the other selected-range values.

For a table without one unique identifier, use a combination of columns. For example, an event list might identify a repeated entry by matching both Event date and Attendee email. This avoids treating the same person’s attendance at two different events as a duplicate. The practical test is: would two records with matching values in these fields definitely describe the same real-world item?

When duplicate records contain different details, the tool does not merge the details or decide which value is more accurate. It keeps one matching row and removes the others. Since the first matching row is retained, sort the complete table first if you have a deliberate reason to put the preferred record first—for instance, the most recently updated record. Make sure to sort the whole table, not just one column, so fields do not become misaligned.

Find duplicates before deleting them

If you are unsure what the tool will remove, identify or review duplicates first. Conditional formatting can highlight repeated values without deleting rows. Select the cells to check, then choose Format > Conditional formatting. Under the formatting rules, choose Custom formula is and enter a formula suited to the selected range.

With values in A2:A and the conditional-formatting range set to A2:A, use =COUNTIF($A$2:$A,$A2)>1 to highlight every value that appears more than once. This flags all instances of a repeated value, including its first occurrence. Review the highlighted cells and decide whether those repetitions are errors or legitimate repeats.

To highlight only later occurrences, with the same values in A2:A and the rule applied starting at A2, use =COUNTIF($A$2:A2,A2)>1. The expanding range counts from the first data row through the current row, so the first occurrence is not flagged and subsequent repeats are. These formulas compare one column; for records that need a multi-column match, you can instead select the relevant fields in the Remove duplicates dialog and inspect the result on a copy.

Use UNIQUE when you need a separate result

The UNIQUE function creates a distinct list without deleting the source data. With source values in A2:A, enter =UNIQUE(A2:A) in an empty cell. The result expands into the cells below and shows each distinct value once, in the order the values first appear. This is useful for making a clean lookup list or a quick summary while preserving the original entries.

Unlike the removal tool, this formula returns the chosen values as a separate result; it does not remove full duplicate records from the original table. If you need complete rows, give UNIQUE a multi-column range, such as A2:C, to return distinct rows across those columns. The rows are considered distinct based on the values in the range provided. Leave enough empty space below and beside the formula for the result to expand; existing cell contents can prevent the output from appearing.

Use the menu tool when you intend to clean the source table. Use UNIQUE when you want a live, formula-based list that updates as source data changes. If you need a fixed list from the formula result, copy it and use Edit > Paste special > Values only in the destination area.

Common problems and how to avoid them

Rows that look the same are not being treated alike: Check whether you selected the correct columns and whether the cells contain extra spaces, different punctuation, or hidden characters. “Alex” and “Alex ” can look almost identical but contain different text. Clean or standardize the source values before comparing them.
Valid records disappear: You may have selected a field that repeats legitimately, such as a customer name, rather than a unique identifier or suitable combination of fields. Undo the change or restore the copy, then choose a more precise set of comparison columns.
The wrong version of a record remains: The tool retains the first matching row it encounters. Sort the full table so the record you want to keep appears first, then run the cleanup again on a safe copy.
Only some columns were selected: Selecting a partial range can put related data at risk. Select the complete table so each row’s associated values are handled together.
A formula result will not expand: A UNIQUE formula needs empty cells for its output. Clear the obstructing cells or place the formula in an area with enough room.

Formatting differences, such as font color or number display style, are not a reliable way to decide whether records are duplicates. Compare the underlying values and the fields that define the record. If values come from imports, inspect a few examples closely before cleaning, since dates, numbers stored as text, and inconsistent spacing can complicate comparisons.

Which method should you use?

Use Data cleanup > Remove duplicates when you want to delete repeated rows from the selected table and keep one copy. It is direct and provides a removal count, but it changes the source data. Use conditional formatting when you want to see repeats and verify them before taking action. Use UNIQUE when you want a separate, updating list rather than a destructive cleanup.

These options solve related but different problems. Conditional formatting is a review aid, not a cleanup: highlighted entries remain in the sheet. UNIQUE produces a distinct output, but the source rows stay in place. The menu command removes duplicate rows from the selected data, so it is the closest match when the goal is to reduce a table to one record for each chosen key.

Frequently Asked Questions

Does Google Sheets keep the first or last duplicate?
It keeps the first matching row in the selected data and removes later matching rows. If you need a particular version to remain, arrange the complete table so that version comes first before running the tool.

Can I remove duplicates based on just one column?
Yes. In the Remove duplicates dialog, select only the column you want Sheets to compare. Use this only when repeated values in that column really mean the records should be treated as duplicates.

Can I highlight duplicates without deleting them?
Yes. Select the range and use Format > Conditional formatting with a custom formula such as =COUNTIF($A$2:$A,$A2)>1 for values in A2:A. The formatting marks repeats but does not alter the data.

Does UNIQUE delete duplicate rows?
No. It returns a distinct list or set of rows in a separate formula result. The source data stays unchanged, so use the Data cleanup command if you intend to remove rows from the original table.

Can I undo removing duplicates?
Immediately after the operation, try Edit > Undo. For safer recovery, duplicate the sheet or preserve a copy of the data before cleanup, particularly if you plan to make additional edits afterward.

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.