Delete Duplicates in Google Sheets: Safe Methods

To delete duplicate rows in Google Sheets, select your data, then choose Data > Data cleanup > Remove duplicates. Confirm whether the range has a header and which columns define a duplicate, then click Remove duplicates; copy the data first if you may need to restore the original.

Remove duplicate rows with the built-in tool

Google Sheets has a built-in command for removing duplicate records from a selected range. It is usually the quickest option when you want to change the sheet itself rather than create a separate, cleaned-up list. The command compares rows using the columns you select and removes repeated matches from the selected data.

Select the cells containing the records you want to check. Include every column that belongs to those records, not just the column that appears to contain duplicates.
Choose Data > Data cleanup > Remove duplicates.
If the first row contains column names, check Data has header row.
In the columns list, select the columns that should determine whether two rows count as duplicates.
Click Remove duplicates. Sheets reports how many duplicate rows it removed and how many unique rows remain.

For example, if a contact table has name, email, and department columns, selecting all three columns removes rows that match across all three. If you select only email, records with the same email count as duplicates even if the name or department differs. That may be exactly what you want, but it is a decision about your data, not just a formatting choice.

How Google Sheets decides what is a duplicate

The key question is which columns define one record. A duplicate full row repeats the same values across all the selected columns. A duplicate based on a key repeats the value or combination of values in only the columns you choose. The other columns can differ, which is why choosing a key carelessly can remove useful information.

Suppose a sales sheet contains order ID, customer, date, and amount. If order ID uniquely identifies a transaction, checking that column alone can find repeated order IDs. If the same customer can place multiple orders, checking customer alone would incorrectly treat valid purchases as duplicates. If there is no single unique key, a combination such as customer plus date plus amount may be more appropriate.

Removal is not the same as merging. The command removes repeated rows; it does not combine nonmatching details from two records into a richer record. When two rows share a name but have different phone numbers, deleting one does not reconcile the phone numbers. Review records that conflict before deciding which one to keep.

Choose the right columns before removing anything

Think about what one row represents and what makes that record unique. This small check can prevent a large cleanup error, especially in sheets with many fields or repeated names.

Use all relevant columns when only identical rows should be removed. Differences in any included field will keep the rows distinct.
Use an ID column when each real-world record has a reliable identifier, such as an invoice number or product code.
Use a group of columns when uniqueness depends on a combination, such as a date and account number.
Do not use a descriptive field alone if it can legitimately repeat. Names, cities, job titles, and product categories are often shared.

Before confirming, read the column choices in the dialog rather than assuming Sheets selected the intended ones. If you selected a range that contains several adjacent tables or extra notes, narrow the selection first. Removing duplicates from a range that excludes related columns can also leave information outside the range out of step with the rows you cleaned. Keeping each record in a complete rectangular range helps preserve row relationships.

Prepare a safe copy and inspect the data

Because Remove duplicates changes the selected cells, preserve a recoverable copy when the data matters. You can duplicate the sheet tab from its tab menu, or copy the range to a new sheet before cleanup. A copy is especially useful for shared workbooks, imports that are hard to recreate, and records that may be needed for an audit or comparison.

Check the selection and the header setting before running the command. If a header is not identified correctly, a column label may be treated as ordinary data; if the range starts below the header but you say it includes one, the first data row may be treated as a header. Also consider sorting or filtering the relevant records to review suspicious groups first. Sorting does not itself remove duplicates, but it can make repeated IDs or names easier to examine.

After removal, check the completion message and scan the cleaned list. If the result is wrong, use Edit > Undo promptly, or press Ctrl+Z on Windows or Chromebook. On a Mac, the usual undo shortcut is Command+Z. Undo may not be a dependable recovery plan after later edits or actions, so a backup copy is safer. In a shared file, consider whether another editor is working in the sheet before making a destructive change.

Use UNIQUE to create a cleaned list instead

If you want to keep the original data untouched, use the UNIQUE function to display distinct rows in another area or sheet. The result is an updated output list rather than a deletion from the source. This is useful for a report, a review step, or a recurring workflow where new source rows may be added.

With records in A2:C100, enter =UNIQUE(A2:C100) in an empty cell. Google Sheets returns the distinct rows from that range, preserving the order in which each distinct row first appears. Make sure the cells below and to the right of the formula are empty enough for the result to expand; otherwise, the array output can be blocked.

UNIQUE compares the rows in the range as a whole in this example. If two rows have the same email but different names, they remain distinct because the rows differ. To get distinct values from one column, use a one-column range, for example =UNIQUE(B2:B100) when emails are in B2:B100. This gives a list of unique email values, but it does not remove or reconcile the corresponding rows in the source table.

To return only values that occur exactly once, rather than one copy of every repeated value, use the optional third argument. With values in A2:A100, enter =UNIQUE(A2:A100,FALSE,TRUE). The FALSE argument tells the function to compare down rows, and TRUE requests values that appear only once. This is a different result from ordinary deduplication: a value appearing twice is excluded entirely, not retained once.

Find duplicates before deleting them

Sometimes the right first step is to identify repeated values and review them. Conditional formatting can highlight duplicates visually, while a helper formula can mark them for filtering. For example, if IDs are in A2:A100, enter =COUNTIF($A$2:$A$100,A2)>1 in a helper cell on row 2 and fill it down. TRUE marks each row whose ID appears more than once in the specified range. This flags every member of a repeated group, including the first occurrence; it does not tell you which copy to delete.

For duplicate keys made from two columns, use COUNTIFS. If customer IDs are in A2:A100 and dates are in B2:B100, enter =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1 beside row 2 and fill down. A TRUE result means that the same customer ID and date combination occurs more than once. Check whether other fields differ before removing a row; those differences may explain why both records exist.

Google Sheets also offers conditional formatting through Format > Conditional formatting. A custom formula can highlight repeated values, but the exact rule depends on the range and whether you want to mark every occurrence or only later ones. A helper column is often easier to audit because it makes the test explicit and can be filtered. Neither method alters the source records by itself.

Common problems and how to address them

Rows that look alike are not treated as identical. Extra spaces, inconsistent punctuation, different date representations, or values stored as text in one row and as numbers in another can affect comparisons. Inspect the underlying cells rather than relying only on how they appear. Google Sheets has other cleanup tools under Data > Data cleanup, including Trim whitespace, which can help with unwanted leading, trailing, or repeated spaces. Review the result before applying cleanup to important data.

Different records disappear because the key is too broad. If you choose only a name column, two people with the same name may be treated as duplicates even when their addresses differ. Undo if necessary, restore the copy, and repeat with a reliable identifier or a more suitable set of columns.

The formula result cannot expand. UNIQUE returns an array of results. Clear obstructing cells in the output area or place the formula in a sufficiently empty part of the sheet. Avoid putting the formula inside the source range if its output could overwrite or conflict with the records you want to inspect.

A cleanup seems to affect related columns incorrectly. Select the complete record range before using the tool. If one table is split into separate ranges, removing rows from only one part can break the relationship between fields. Make a copy and check the table boundaries before trying again.

Remove duplicates or keep a formula-based view?

Use Remove duplicates when the source itself should be cleaned and you have decided which columns define a repeated record. It is direct and leaves ordinary cell values rather than a formula-generated view. Use UNIQUE when the original data should remain available, when you want a distinct list for a report, or when you want the output to update as the source changes within the formula range.

These options solve related but different problems. Remove duplicates deletes repeated rows from the selected range. UNIQUE produces a separate result, and it does not merge records or automatically choose the most complete row. A review formula such as COUNTIF or COUNTIFS only flags matches. For a careful cleanup, it can be sensible to flag and inspect first, then remove rows only after confirming the correct key and preserving an original copy.

Google Sheets is browser-based, so its menus and commands differ from Excel. In Excel for Microsoft 365, the Remove Duplicates command is on the Data tab, while Google Sheets uses Data > Data cleanup > Remove duplicates. The general idea is similar, but do not assume that a menu path or dialog option from Excel exists in Sheets.

Frequently Asked Questions

Does Google Sheets keep the first duplicate?
Remove duplicates retains one copy of a repeated record and removes the other matching rows. If it matters which version survives, sort or review the records first, and keep a backup so you can verify the result. Do not rely on deduplication to choose the most complete or newest record for you.

Can I remove duplicates based on just one column?
Yes. In the Remove duplicates dialog, select only the column that defines a duplicate. Rows with a matching value in that column may be removed even when their other fields differ, so make sure those differences do not represent separate valid records.

Can Google Sheets delete duplicates without changing the original data?
Yes. Put a UNIQUE formula in an empty area or another sheet to create a distinct output list while leaving the source range intact. For a one-time manual result, you can also copy the source to another sheet and run Remove duplicates on that copy.

How do I find duplicates without deleting them?
Use a helper formula such as COUNTIF for a single key column or COUNTIFS for a combination of columns, then fill it down to flag repeated records. You can also use Format > Conditional formatting to highlight matches. Review flagged rows before deciding what to remove.

Why does Remove duplicates leave records that look the same?
The cells may differ in their underlying values, for example because of extra spaces, punctuation, text-versus-number differences, or other inconsistencies that are not obvious on screen. Check the cells and normalize the data only when appropriate, then run the cleanup on a copy or after confirming the intended comparison.

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.