How to Remove Duplicate Rows From a CSV File

6 min readPipeSheets Team

Duplicate rows inflate your counts, skew reports, and get imports rejected by tools that enforce unique keys like email or SKU. Removing them looks like a one-click job, but the default tools only catch exact matches, so "John@Example.com" and "john@example.com " survive as two separate records. Here's how to find the duplicates, normalize the near-matches so they actually match, and remove them properly.

Where Duplicates Come From

Knowing the source tells you how many to expect and which column to deduplicate on. The usual suspects: merging two exports that overlap (last month's list plus this month's), a customer signing up twice with the same email typed differently, a form or sync job that fired twice, and copy-paste assembly of a master sheet from several tabs. Merged files are the worst offenders, because each source system formats the same value its own way — one lowercases emails, the other preserves what the customer typed — which is exactly the kind of duplicate the one-click tools miss.

First, Count Them: Find Duplicates Before Removing

Before deleting anything, measure the problem. In Excel or Google Sheets, put =COUNTIF(A:A,A2) next to your key column (email, SKU, order ID) and fill it down: any value above 1 is a duplicate, and filtering that helper column shows you every copy side by side. This matters because it lets you sanity-check the removal afterward — if COUNTIF says 87 duplicate rows and Remove Duplicates reports removing 30, the other 57 are near-matches hiding behind formatting differences, and you'll know the file isn't actually clean yet.

Method 1: Remove Duplicates in Excel

Open the CSV in Excel, select your data, then go to the Data tab and click Remove Duplicates. In the dialog, confirm "My data has headers" is checked, then choose which columns define a duplicate. This choice is the whole game: check every column and only rows identical across all fields are removed; check just Email and any repeated address counts as a duplicate even if the name or phone differs between the copies. For contact lists, deduplicating on the email column alone is almost always what you want. Excel keeps the first occurrence of each value and deletes the rest, so sort the file first if you care which copy survives — for example, newest record on top so the most recent data wins.

Watch out: Excel's Remove Duplicates is an exact, case-insensitive match across the columns you pick, but it does not trim whitespace. "jane@x.com" and "jane@x.com " with a trailing space are treated as different values and both survive. And one unrelated warning for CSVs: if your file has ZIP codes or SKUs with leading zeros, Excel strips them the moment it opens the file — clean the file outside Excel if those columns matter.

Method 2: Remove Duplicates in Google Sheets

Upload the CSV to Google Drive, open it in Google Sheets, then use Data > Data cleanup > Remove duplicates. Like Excel, you pick the columns that define a duplicate, it keeps the first occurrence, and it reports how many rows were removed. It's free and works from any browser, but it has the same blind spots as Excel: untrimmed spaces and inconsistent casing can hide duplicates. Sheets does have one advantage — Data cleanup > Trim whitespace sits right next to Remove duplicates, so run the trim first, then dedupe.

Deduplicating Across Two Files

A common variant: you have last month's export and this month's, and you need one list without the overlap. The simplest reliable approach is to stack them — paste one file's rows below the other's (make sure the columns line up and delete the second header row), add a source column if you want to know where each record came from, and then run the same normalize-and-dedupe workflow on the combined sheet. Sort so the file you trust more is on top before removing duplicates, since the first occurrence is the one that survives. Trying to eyeball the overlap between two open spreadsheets, by contrast, is how duplicates get through in the first place.

Why "Exact Match" Isn't Enough

Most duplicates in real-world exports aren't byte-for-byte identical. They differ in ways a human reads as the same value but a computer reads as different — and merged files make these near-misses the rule, not the exception.

These all refer to the same person but won't match by default:

john.smith@example.com
John.Smith@example.com   (different case)
john.smith@example.com    (trailing space)
 john.smith@example.com   (leading space)
JOHN.SMITH@EXAMPLE.COM   (all caps)

To catch these, you have to normalize the column first: trim whitespace and lowercase the values, then deduplicate. Skip that step and your "clean" file still has duplicates hiding in plain sight. The same trap applies to SKU columns ("ab-100" vs "AB-100 "), company names padded with non-breaking spaces from web copy-paste, and phone numbers stored with and without separators.

The Reliable Workflow: Normalize, Then Deduplicate

Deduplicate in this order to catch the near-matches:

Trim leading and trailing whitespace from the key column(s)
Standardize case (lowercase emails, consistent casing for SKUs)
Standardize null and placeholder values ("N/A", "none", blanks) so empty-key rows group together instead of surviving as fake uniques
Then remove duplicate rows on the cleaned key column
Re-run the COUNTIF check to confirm every key value now appears exactly once

The normalization is the part worth automating, because it's the part people skip. PipeSheets handles it in one pass: upload the file, add a trim whitespace step, a case transform to lowercase the email or SKU column, and standardize nulls (or just run Quick Clean, which covers the trim and nulls automatically). Preview the result, download the normalized CSV, and then run Excel's or Google Sheets' Remove Duplicates on it — with the formatting differences gone, the exact-match tools finally catch every copy.

To be clear about the division of labor: PipeSheets doesn't delete duplicate rows itself — its job is making the duplicates visible to the tool that does. Normalizing case and whitespace is what turns five "different" versions of john.smith@example.com into five identical values that any dedupe tool removes in one click. Save the normalization as a pipeline and every future export gets the same prep automatically.

Edge Cases to Handle Deliberately

Before you trust the deduplicated file:

Which copy survives: both Excel and Sheets keep the first occurrence — sort so the record you want to keep (usually the newest or most complete) comes first
Conflicting data across copies: two rows with the same email but different phone numbers means dedupe silently discards one phone number — filter the COUNTIF helper and review conflicts before removing
Blank keys: rows with empty email cells all match each other, so a whole-file dedupe can collapse them into one — remove or exclude empty-key rows first
Genuinely different people: two customers can share a mailbox — deduplicating on email alone merges them by design, so pick your key column knowingly
Typo duplicates: john.smith@gmial.com is a different string, and no exact-match tool will pair it with the gmail.com version — those need manual review

Deduplication is really two jobs: normalizing the data so identical things look identical, then removing the repeats. The second job is one click in any spreadsheet. Do the first job properly — trim, lowercase, standardize — and the click actually works.