Remove duplicates in Google Sheets without deleting valid orders
Choose the right duplicate key in Google Sheets, compare Remove duplicates with UNIQUE, and verify the result using a worked order-table example.

👁 113 reads
To remove duplicate rows in Google Sheets, select the complete table and use Data > Data cleanup > Remove duplicates. Before confirming, decide which columns identify the same record: matching email addresses do not make two different orders duplicates.
Decide what one row represents
I begin a cleanup by writing one sentence: “Each row represents an order,” or “Each row represents a customer.” This is the decision that determines which records should survive. The spreadsheet cannot infer your business meaning from the fact that two cells look alike.
Consider this illustrative order table. The amounts are sample values for explaining the calculation, not figures from a real business.
| Order ID | Customer | Amount |
|---|---|---|
| A101 | alex@example.test | 40 |
| A101 | alex@example.test | 40 |
| A102 | alex@example.test | 25 |
| A103 | sam@example.test | 60 |
| A104 | sam@example.test | 15 |
The raw table has 5 rows and totals 180. If the second A101 row is a repeated import, the intended result has 4 orders totaling 140. But if you deduplicate on the customer column alone, you collapse Alex’s two orders and Sam’s two orders into just two customer records. That is a different dataset.
The distinction matters outside sales. In an attendance sheet, the same person can appear at several sessions. In an invoice export, the same invoice can legitimately have several line items. In a mailing list, repeated addresses may be the very thing you want to remove. The correct key depends on the row’s purpose.
For an order-line export, the key might be the combination of order ID and line ID. For a customer directory, it might be an assigned customer identifier. I avoid treating a person’s displayed name as a unique identifier when the source system offers something more precise.
Use the cleanup command on a copy
Make a working copy of the table before applying an operation that removes rows. Give the copy a name that explains the rule, such as orders-dedup-by-order-id. Keep the imported table unchanged until the result is accepted.
Select the table, open Data > Data cleanup > Remove duplicates, indicate whether the selection has headers, and choose the comparison columns. Google notes that identical values can count as duplicates despite differences in letter case, formatting or formulas. Selecting a single cell can cause the operation to cover the surrounding range, so explicitly selecting your table is easier to inspect. Google’s cleanup instructions.
I would write down the intended comparison before clicking the final button:
- One row per order: compare the order identifier, after checking what that identifier means in the source export.
- One row per order line: compare the order identifier together with the line identifier.
- One copy of each complete record: compare every relevant data column.
- One row per customer: use the customer identifier, then decide separately how to reconcile different contact details.
The last case needs more than deletion. Two records for the same customer might contain different phone numbers or a newer delivery address. Choosing which values to preserve is a reconciliation task. A duplicate-removal command does not decide which version is authoritative.
Use UNIQUE when you want a separate result
The UNIQUE function creates a distinct result from a source range. Google documents that it returns unique rows in their first-appearance order. Its optional exactly_once argument has a different purpose: returning only entries that do not repeat. UNIQUE documentation.
For the sample data in A2:C6, a separate output area can use:
text
=UNIQUE(A2:C6)
This compares complete rows. It is appropriate for the sample because both A101 rows are identical across all three columns. It would not remove a second A101 record whose amount differed. That disagreement deserves investigation rather than silent erasure.
Compare it with:
text
=UNIQUE(B2:B6)
That formula produces a list of distinct customers. It does not produce a cleaned order table. The output is useful for counting or reviewing customers, but attaching the original amounts beside it would not reliably associate each customer with the correct orders.
The exactly_once option is also easy to misread. In the sample, asking for complete rows that occur exactly once excludes the repeated A101 record entirely. It does not keep a representative A101 order. Use that option only when your question is “Which records never repeat?”
| Goal | Suitable approach | Check afterwards |
|---|---|---|
| Remove repeated imported rows | Cleanup on a copied table | Remaining rows and total |
| Keep the source and show distinct rows | UNIQUE on the full data range | Output width and contents |
| Build a list of customers | UNIQUE on the customer column | No order amounts attached by position |
| Find entries that occur only once | UNIQUE with exactly_once | Repeated entries are absent |
Flag repeated order IDs before removing anything
For the sample table, select A2:C6, open Format > Conditional formatting, and choose Custom formula is. Use this rule:
text
=COUNTIF($A$2:$A$6,$A2)>1
The fixed range counts occurrences in the order-ID column. The row reference changes for each row being formatted, while $A keeps the comparison on the ID. In this particular example, both A101 rows should be highlighted; A102, A103 and A104 should not. Choose a visible fill and finish the rule. Google documents custom formatting rules.
Highlighting identifies a repeated key, not a permission to delete. Change the second A101 amount to a different value in a disposable copy: it should still be highlighted, but now you have conflicting records to reconcile. If your export uses blank IDs, wildcards or identifiers where letter case matters, do not apply this sample rule unchanged. COUNTIF’s matching rules matter to the result.
This gives me a review step before destructive cleanup. I can compare the full highlighted records, decide which import is authoritative, and preserve the other orders placed by the same customer.
Verify the result before using it elsewhere
I check the sample table against three independent expectations: the surviving order identifiers, the number of rows, and the sum of amounts. A row count alone cannot show that you removed the right row.
For a larger file, choose a few records to inspect deliberately. Include a genuine duplicate, a customer with several valid orders, and a record whose values differ between imports. This creates a meaningful review set instead of checking only the first screen of the spreadsheet.
Whitespace deserves a separate pass. Google’s trimming tool removes ordinary extra spaces but does not remove non-breaking spaces. The UNIQUE documentation also warns that hidden text can make apparently identical rows differ. If two entries survive unexpectedly, compare the actual cell contents before repeatedly running cleanup. Google’s cleanup instructions.
Do not normalize every column indiscriminately. A product code, customer identifier and free-text comment have different meanings. First preserve the source, then make the intended normalization visible in a working column or copied table.
Once the checks agree, record the rule used and the date of the cleanup. That note lets another person reproduce the decision when next month’s export arrives. The useful deliverable is a table whose remaining rows have a clear meaning.
FAQ
Should I select only the column containing duplicates?
Select the complete table when removing rows, then choose the comparison columns in the dialog. This keeps the record structure explicit while you decide the matching rule.
Can two rows have the same email and still be valid?
Yes. In the example, one customer placed different orders. An email-only rule would collapse valid transactions.
Does UNIQUE change the imported source table?
It creates a formula result from the selected range. Keep the output in a separate area with room for its rows and columns.
Why do visually identical values survive?
Hidden characters or different underlying contents can matter. Inspect the cells and your comparison columns before removing more data.
Is a smaller row count proof that cleanup worked?
No. Compare identifiers, valid repeat activity and relevant totals against the original. The sample result should contain four orders totaling 140.
Google · Your sourcesQuantslant higher in your Google - one clickAdd to preferred sources →Sources
- Google, Conditional formatting and custom formulas. Read 16 November 2026. https://support.google.com/docs/answer/78413?hl=en
- Google, COUNTIF matching rules. Read 16 November 2026. https://support.google.com/docs/answer/3093480?hl=en
- Google, Split text, remove duplicates, or trim whitespace. Read 16 November 2026. https://support.google.com/docs/answer/6325535?hl=en
- Google, UNIQUE function. Read 16 November 2026. https://support.google.com/docs/answer/10522653?hl=en
- Worked example: original synthetic order table supplied in this article; calculations checked in the accompanying project validation.
More on this from QUANTSLANT
ai tools·Export ChatGPT conversations and check the archive before movingExport your ChatGPT conversations, open the current archive viewer, and check the files before deleting chats or moving to another workspace.