Remove Duplicates in Google Sheets Without Deleting the Wrong Rows
Duplicate rows are a data problem before they are a button to click
A contact list with repeated customers, a product sheet with the same SKU twice or a survey export containing repeated submissions can distort counts and lead to mistakes. Google Sheets offers more than one way to handle duplicates, but the correct method depends on what “duplicate” means for your data. Two rows can share a name and still represent different people. Two products can share a description but have different variants. Removing every repeated value without defining the key may delete legitimate records.
This guide compares three practical approaches: a one-time Remove duplicates operation, a separate UNIQUE formula result and a helper-column check using COUNTIF or a composite key. The methods work for international English-language users of Google Sheets and do not depend on one country's regulations. They are also useful in agency workflows, school projects and small-business catalogues.
Decide which fields identify a repeated record
Before touching the sheet, ask what should be unique. In a mailing list, an email address may be the best key, although shared addresses and aliases can complicate the rule. In an order sheet, the order ID might be unique even when the customer's name repeats. In a product catalogue, the combination of parent product and variant may matter more than a display name. Write the rule in plain language before selecting a tool.
For example, a sheet with columns Name, Email and City might contain two entries for “Alex Morgan” with different emails. If you remove duplicates using Name alone, you may lose a legitimate person. If you use Email alone, you may retain duplicate people who entered two addresses. There is no universal correct answer; the key must fit the task and the consequences of a false match.
Make a recoverable copy first
Duplicate removal changes data. Before cleaning a shared or important sheet, duplicate the tab or make a copy of the spreadsheet. Record the date and the columns used as the deduplication key. If collaborators are editing at the same time, agree on a short pause or work in a separate copy so new entries are not mixed into the cleanup. Version history can help recover changes, but a clear working copy makes review easier.
Keep an untouched raw-data tab if the sheet is fed by forms or imports. A common professional pattern is Raw, Clean and Review: Raw preserves incoming records, Clean contains the approved working table and Review records ambiguous matches. That separation prevents a later import from silently undoing your decisions.
Method one: Data cleanup and Remove duplicates
Google's official Sheets help explains the desktop workflow: select the data range, open Data, choose Data cleanup and then Remove duplicates. Choose whether the selected range has a header row and which columns should be compared. Sheets then removes rows considered duplicates under that selection. This is the fastest option for a one-time cleanup when you know the right comparison fields and are comfortable changing the selected data.
The selected columns are crucial. If you compare every column, two rows with the same email but different notes may survive as different records. If you compare only the email column, one row may be removed even though its notes contain information worth merging. Review a sample first and preserve a copy. Google's help also notes that identical values with differences in letter case, formatting or formulas may be treated as duplicates in the tool; do not rely on cosmetic formatting to distinguish records.
Method two: UNIQUE creates a separate result
The UNIQUE function returns distinct rows or values from a specified range. Instead of deleting entries from the original table, it produces a result elsewhere. That makes it useful when you want a live list of unique categories, customer names or product IDs while preserving the underlying records. For a single column of values in A2:A100, a simple example is =UNIQUE(A2:A100) placed in an empty area.
For a multi-column range, =UNIQUE(A2:C100) treats whole rows as the units of comparison. A repeated email with a different city can remain because the row differs. Decide whether that behaviour matches the goal. Also remember that a formula result can expand as source data changes, so leave space for it and avoid typing manual values into the spill area. If the source contains blank rows or inconsistent spacing, clean or filter it as needed.
Method three: flag duplicates before removing anything
A helper column can make the review process safer. Suppose column B contains email addresses and the first data row is row 2. In a new column, a formula such as =IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"Review duplicate","First occurrence")) labels later repeats while leaving the source untouched. Fill the formula down the relevant rows and filter for the review label. This is useful when a human must decide which record is more complete.
COUNTIF counts matching values in a range; Google documents its syntax and notes that it is not case-sensitive. That means this approach is not suitable when your business rule treats uppercase and lowercase as different identifiers. It also does not automatically solve differences such as extra spaces, punctuation or alternate email addresses. Treat the formula as a triage tool, not as an identity-verification system.
Normalise carefully: spaces, case and hidden differences
A frequent cause of missed duplicates is inconsistent input. One row may contain a trailing space, another an extra internal space, and another a non-breaking space copied from a website. Google Sheets has a Trim whitespace option under Data cleanup, but its help warns that non-breaking spaces are not trimmed by that command. A careful workflow may need an additional formula or manual inspection before comparing values.
Normalisation should be documented. Converting email addresses to a consistent case may be reasonable for many practical mailing-list tasks, while changing case in an identifier that is defined as case-sensitive could be wrong. Do not strip punctuation from names, addresses or product codes without considering whether the punctuation carries meaning. Always compare a few known records before applying a rule to thousands of rows.
When a composite key is better than one column
Consider a product list with columns Product, Size and Colour. A shirt can appear several times because it has multiple sizes and colours. Removing duplicates by Product would destroy valid variants. Instead, create a helper key that combines the fields that define a unique variant. You might use a formula based on LOWER, TRIM and concatenation with a separator unlikely to occur in the data, then review the key before deleting any rows.
For example, a catalogue manager might define one record as a particular SKU rather than a combination of descriptive attributes. That is even safer if SKU rules are enforced consistently. The point is not to build the cleverest formula; it is to match the deduplication rule to the real-world object represented by each row.
Check the results with counts and spot checks
After cleanup, compare the number of source rows, removed rows and remaining rows. Check a few records you know were duplicates and a few that should have remained separate. Review any merged notes or lost columns. If you are preparing a list for an email campaign or import, validate the cleaned file in a staging copy before sending or uploading it.
For large data, avoid entire-column formulas when a bounded range will do, particularly if the spreadsheet already contains complex calculations. Google advises that removing duplicates can improve lookup performance and that repeated heavy formulas may slow Sheets. A simple, documented cleaning pass is often more reliable than a stack of nested formulas that nobody else can audit.
Frequently asked questions
Does Remove duplicates keep the original data?
It modifies the selected data. Make a copy or duplicate the tab first. Use UNIQUE when you want a separate distinct list without deleting source rows.
Why are two rows with the same name not removed?
If other selected comparison columns differ, the rows may not count as duplicates. Check the selected key columns and whether the records genuinely refer to the same person or item.
Can I highlight duplicates instead of deleting them?
Yes. A helper formula or conditional formatting can flag repeated values for review. This is often safer for customer, financial or inventory data.
Why does UNIQUE show two entries that look identical?
Look for extra spaces, non-breaking spaces, hidden characters or other field differences. Inspect the raw values before normalising them.
A repeatable cleanup workflow
Define the unique key, preserve the raw data, flag suspicious records, inspect exceptions, remove only confirmed duplicates and record what changed. For an ongoing workflow, maintain a clean output tab rather than repeatedly editing the source. Revisit Google's official Sheets help if the menu location changes; the core data-quality principle stays the same: deduplication is a decision about records, not just a command in a toolbar.
