Google Sheets Duplicate Rows: Find and Remove Them

Yes. In Google Sheets, select the data range and choose Data > Data cleanup > Remove duplicates to remove repeated rows; for a safer review, use conditional formatting or a formula to identify them first. The key decision is whether a duplicate means every selected column matches or only a particular field, such as an email address.

What Counts as a Duplicate Row?

A duplicate row is not always a row whose every cell matches. In a customer list, for example, two records might share an email address but have different names or dates. If email is the unique identifier, those records may count as duplicates for your purpose even though the rows are not identical. Conversely, two orders from the same customer may be legitimate separate records, so removing rows based on customer name alone would lose information.

Before cleaning a sheet, decide which columns define a duplicate. Common choices include:

Every field: the row is a duplicate only when all selected columns match.
A unique identifier: for example, the same email address, invoice number, or product code.
A combination of fields: for example, the same customer ID and order date together.

This distinction matters because Google Sheets checks the columns you select in the Remove duplicates dialog. It does not know which records are meaningful in your business or project. A tool can detect repeated values according to your selection, but deciding whether the repeat is an error is a data-quality judgment.

Remove Duplicate Rows with the Built-In Tool

The built-in cleanup tool is the quickest choice when you want to delete repeated records from a range. These steps refer to Google Sheets in a web browser:

Make a copy of the spreadsheet or duplicate the sheet tab if you may need to restore the original records.
Select the whole table, including every column that should be part of the comparison. Avoid selecting only one column if you intend to remove full duplicate rows.
Choose Data > Data cleanup > Remove duplicates.
In the dialog, indicate whether the range has a header row. Then check the columns that should determine whether rows are duplicates.
Choose Remove duplicates and review the confirmation message, which reports how many duplicate rows were removed and how many unique rows remain.

For example, suppose columns A through C contain Name, Email, and Signup date. If you select all three columns, records are treated as duplicates only when the selected data across those columns matches. If you select only Email, rows sharing an email can be treated as duplicates even if their names or dates differ. In that second case, the tool keeps one matching record and removes the others, so consider which version you want to retain before proceeding.

The command changes the selected data rather than producing a separate results table. If you remove the wrong rows, use Edit > Undo immediately, or restore your backup. Undo may not be available after later edits, closing the file, or other actions, so a copy is a more dependable safeguard for important data.

Choose the Right Columns Before Cleaning

Column selection determines the outcome. A common mistake is to select an entire table when the real goal is to deduplicate by one identifier, or to select just the identifier when the goal is to remove only completely repeated rows. Think through what should happen to a realistic pair of records before running the command.

Imagine these rows:

Jordan Lee, jordan@example.com, April 2
Jordan Lee, jordan@example.com, April 9

They are different full rows because the dates differ. If date is important, include it in the comparison. If the email must appear only once, select the email column as the comparison field, but first decide whether to keep the earliest signup, the latest one, or another preferred record. The cleanup tool does not make that business decision for you; it retains one occurrence and removes the other matching rows.

For a compound identifier, select all the fields that together define a record. A booking might be unique by customer ID plus booking date, while neither field alone is unique. Avoid selecting unrelated columns: including a changing note or timestamp can make otherwise repeated records appear different.

Review Duplicates Without Deleting Them

If you are not certain which rows to remove, flag them first. For a simple identifier in column A, you can use a helper column to mark later occurrences. With the identifier in A2:A and a header in row 1, enter =COUNTIF($A$2:A2,A2)>1 in B2 and fill it down. For ordinary values, the first occurrence returns FALSE and later occurrences return TRUE. Filter column B for TRUE to inspect the candidate repeats without deleting them.

To flag repeated full rows across columns A through C, with data beginning in row 2, use a helper column and enter =AND(COUNTA(A2:C2)>0,COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)>1) in D2, then fill down. The first occurrence of each nonempty combination returns FALSE; a later matching row returns TRUE. The COUNTA check prevents an entirely blank row from being labeled as a duplicate. Extend the formula to include every column that defines a full-row match.

You can also use conditional formatting to make repeats easier to scan. Select the relevant cells, choose Format > Conditional formatting, and create a custom formula rule. For one identifier in column A, a rule based on =COUNTIF($A$2:$A,$A2)>1 can highlight every occurrence of a repeated value when applied to the appropriate range. Highlighting all occurrences differs from the helper-column approach above, which marks only later occurrences.

Create a Separate List of Unique Rows

When you want to preserve the source data, Google Sheets can return distinct rows in a separate area with the UNIQUE function. With a header in row 1 and source records in A2:C, enter =UNIQUE(A2:C) in an empty cell outside the source range. The result is a list of rows that occur once in the output, with repeated full-row matches represented once. The source remains unchanged, and the output updates when the source data changes.

Leave enough empty space below and to the right of the formula for the results to expand. If existing content blocks the output, Sheets displays an error instead of overwriting those cells. UNIQUE compares rows across the supplied range by default; it does not deduplicate by one chosen field while retaining arbitrary values from the other columns. For that task, use the built-in tool with the intended column selection or first define a rule for which record should survive.

For instance, if rows 2 and 4 in A2:C contain identical values and row 3 is different, the formula returns the row 2 values once, followed by the distinct row 3 values. It is useful for reports and review lists, but it is not the same as physically deleting duplicates from the original table.

Why Duplicates May Not Match as Expected

Two cells that look alike on screen may contain different underlying data. A date displayed as 1/5/2026 might be stored as a date value, while a visually similar entry could be text. Numbers can also be displayed with rounded formatting even when their stored values differ. Before cleanup, check the underlying entries and the number or date formats if apparently identical rows are not being grouped as expected.

Extra spaces are another frequent cause of mismatches. A value such as alex@example.com may differ from one with a leading or trailing space. Standardize data before deduplicating—for example, trim copied text in a helper column, inspect the results, and then use the cleaned values as the comparison field. Be cautious with case changes, punctuation, and spelling: normalizing them can merge records that are intentionally distinct.

Blank rows, partially completed records, and formulas deserve attention too. A row with an empty email is not necessarily a duplicate just because another row also has an empty email. Decide whether incomplete rows should be excluded or reviewed separately. Similarly, a formula may display the same result as another cell while referring to different source data; verify that removing one record will not affect a calculation or workflow that depends on it.

Safer Cleanup for Shared or Important Sheets

Google Sheets is designed for collaboration, so a cleanup can affect other people who use the same file. Tell collaborators before making a substantial change, and consider working in a copy or a separate tab. For a large or sensitive list, keep the original records and create a reviewed, deduplicated version rather than treating deletion as the only option.

Keep a copy of the original data, especially before deduplicating by a single key.
Sort or filter to inspect repeated identifiers and decide which record should remain.
Check totals, row counts, and important summary formulas before and after cleanup.
Preserve a source or audit column if you need to trace a cleaned record to its original row.
Run the cleanup on the intended range, not on unrelated notes or data elsewhere on the sheet.

These checks help catch a different kind of error: a technically correct duplicate removal that removes the wrong version of a record. If rows contain different notes, statuses, or transaction details, retaining the first occurrence may not be appropriate. Resolve conflicting records deliberately before deleting anything.

Google Sheets Compared with Excel

Google Sheets and Microsoft Excel both offer ways to remove or identify duplicate data, but their menus and dialog layouts differ. The steps above use Google Sheets’ browser interface and its Data > Data cleanup > Remove duplicates command; they are not Excel instructions. Excel for Microsoft 365 on Windows has its own Remove Duplicates command on the Data tab, with options for indicating headers and choosing comparison columns.

Functions such as COUNTIF, COUNTIFS, and UNIQUE are also available in Google Sheets, though spreadsheet behavior and available options can differ between products. If a workbook is shared between Sheets and Excel, verify formulas and the displayed results in the application where the file will be used. A formula-generated unique list is especially useful when you need a refreshable output; the built-in removal command is more direct when you intend to change the data itself.

Frequently Asked Questions

Does Remove duplicates delete entire rows in Google Sheets?
Yes. It removes repeated rows from the selected range according to the columns you select in the dialog. Make sure the range includes every column you want to keep together, and save a copy first if the data matters.

Can I remove duplicates based on just one column?
Yes. Select the table, open the cleanup dialog, and select only the identifier column for the comparison. Sheets can then remove rows that repeat that value, so confirm which occurrence should remain before you accept the change.

Can I find duplicates without deleting them?
Yes. Use a helper formula such as COUNTIF to flag repeated values, conditional formatting to highlight them, or UNIQUE to create a separate list of distinct rows. These approaches let you review candidates while leaving the original records in place.

Why does Google Sheets still show duplicates after I clean the data?
The rows may differ in a selected column, contain extra spaces, use different underlying values or formats, or repeat a field you did not include in the comparison. Check the relevant cells and choose the comparison columns that match your actual definition of a duplicate.

Does UNIQUE remove duplicates from the original data?
No. UNIQUE returns a separate result list and leaves the source range unchanged. Use the built-in Remove duplicates command when you want to delete repeated rows from the selected data.

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.