How to Remove Duplicate Rows After Converting CSV to Excel
To remove duplicate rows after converting CSV to Excel, select the data and use Data > Remove Duplicates, choosing which columns define a duplicate. CSV exports are duplicate-prone: systems re-export overlapping date ranges, concatenated files repeat header rows as data, and sync glitches write the same record twice. Dedupe before you analyze — every total, count, and average is wrong until you do. (If you have not converted the file yet, the free CSV to Excel converter produces the workbook in one step.)
Method 1: Data > Remove Duplicates (Fastest)
- Select any cell in the imported data (or the full range).
- Go to Data > Remove Duplicates.
- In the dialog, tick the columns that define uniqueness:
- All columns ticked: removes only rows identical in every column — the safe default.
- Subset ticked (e.g., Order ID only): keeps the first occurrence of each ID and deletes the rest — use when one column is the true key.
- Click OK. Excel reports how many duplicates it removed and how many unique rows remain.
Critical: Remove Duplicates keeps the first occurrence and deletes later ones. If "first" matters (earliest timestamp, original entry), sort the data appropriately before deduping.
Work on a copy. Dedupe is destructive — there is no undo after save-and-close. Duplicate the sheet (right-click tab > Move or Copy > Create a copy) before you start.
Method 2: The UNIQUE Function (Non-Destructive)
If you want the deduplicated list without touching the source:
=UNIQUE(Data)placed in an empty area spills the unique rows. Variants:
- Unique values from one column:
=UNIQUE(Data[Email]) - Unique combinations of two columns:
=UNIQUE(Data[[Name]:[Email]]) - Exactly-once values (drop anything appearing more than once):
=UNIQUE(Data[OrderID], TRUE)
The source stays intact; the spill range updates if the source changes. This is the best option when you are unsure about the dedupe criteria and want to experiment safely.
Method 3: Power Query (Repeatable)
For files that arrive regularly with the same duplication problem:
- Data > From Text/CSV > Transform Data.
- Select the key column(s), then Home > Remove Rows > Remove Duplicates.
- Close & Load. The dedupe replays on every refresh.
Power Query also offers Remove Errors and Keep Duplicates (to inspect what would be removed) — use Keep Duplicates first on a new file to see what you are about to delete before committing.
What Counts as a Duplicate? (Decide Before You Click)
| Definition | Columns to tick | Keeps |
|---|---|---|
| Exact full-row copy | All columns | First identical row |
| Same order/customer twice | Order ID (or Customer ID) | First occurrence per ID |
| Same person, either email | Email only | First row per email |
| Same transaction, any formatting | ID + Amount + Date | First per combination |
The most expensive mistake is ticking too few columns: deduping on Name alone deletes two different people named "Ali Khan". When in doubt, tick more columns, inspect the removed count, and narrow down.
Tricky Duplicates That Exact Matching Misses
Whitespace differences. "ali@x.com" vs "ali@x.com " (trailing space) are different values to Excel. Clean first: Power Query Transform > Format > Trim, or a helper column with =TRIM().
Case differences. "Order-101" vs "ORDER-101" — Remove Duplicates treats these as different (case-sensitive in Power Query; Excel's dialog is case-insensitive, inconsistently). Normalize with UPPER/LOWER before deduping.
Near-duplicates. "Robert Smith" vs "Rob Smith" vs "R. Smith" are not duplicates to any automatic tool — they need human judgment or fuzzy matching (Power Query's Merge with fuzzy options, or the Fuzzy Lookup add-in). Flag them; do not auto-delete.
Header rows repeated as data. Concatenated CSVs often contain the header row mid-file. These look like duplicates of nothing (the header text is unique) — filter them out by the header text before deduping, or they survive and pollute sorts and filters.
False duplicates from formatting. Two rows identical except one has a trailing space are flagged different; two rows where a code column lost leading zeros ("001" vs "1") may be flagged same. Fix types and formatting at import before trusting dedupe counts.
Deduplicating Across Multiple Files
Duplicates often span files, not just rows within one: January's export overlaps February's by a week, or two systems export the same orders. The fix is append-then-dedupe:
- Append first. In Power Query: Home > Append Queries, stack the files into one table. (The combine guide covers multi-file appends.)
- Sort by recency if the files overlap in time — newest file's rows first — so "keep first" retains the latest version of each record.
- Remove Duplicates on the key columns, as in Method 3 above.
- Verify the overlap. Before deduping, note each file's row count; after, the total should equal the sum minus the expected overlap. If the removed count is zero, the files did not overlap (or the keys do not match — check types). If it is enormous, the keys are too loose.
Which file wins? When the same key exists in two files with different values (an order's status changed between exports), keeping the first row after a recency sort keeps the newest data. Document this choice — "newest export wins" — so future analysts know the rule.
Keys across files must match in type. File A's OrderID as text and File B's as number will never match, and the dedupe will silently keep both. Standardize types in Power Query before appending — this is the number-one cause of "dedupe didn't work across files."
A dedupe audit table. Keep a tiny log: file names, row counts in, rows removed, rows out, date. When someone asks "why does February show fewer orders than the raw export?", the audit table answers in seconds. It also catches the day the source system starts emitting real duplicates — the removed count jumps, and you investigate instead of shrugging.
FAQ
Does Remove Duplicates keep the first or last occurrence?
The first. Sort the data so the row you want to keep comes first (e.g., newest timestamp first if you want to keep the latest record per ID).
How do I find duplicates without deleting them?
Use Conditional Formatting > Highlight Cells Rules > Duplicate Values to see them, or =COUNTIF($A$2:$A$1000, A2)>1 in a helper column to flag them. Inspect first, delete second.
Can I undo Remove Duplicates?
Only with Ctrl+Z before saving and closing. After save-and-close, undo is gone — which is why you work on a copy of the sheet.
What's the difference between UNIQUE and Remove Duplicates?
UNIQUE is a formula: non-destructive, live-updating, outputs to a spill range. Remove Duplicates is a one-time action: destructive, but works in-place and handles the "keep first per key" logic with a dialog.
My duplicate count looks wrong. What should I check?
Trim whitespace, normalize case, fix column types (text vs number keys), and check for repeated header rows from concatenated files. Nine times out of ten, the "duplicates" were a cleaning problem, not a counting problem.
Final Checklist
- Dedupe criteria decided: which columns define "same record" — documented, not guessed.
- Worked on a copy; original import untouched.
- Data cleaned first: trimmed, case-normalized, types fixed, stray header rows removed.
- Removed count sanity-checked against expectations (0 removed or 90% removed both deserve a second look).
- For recurring files: dedupe built into Power Query so refresh replays it.
If all five checks pass, your imported data is genuinely unique — every total and average from here on is trustworthy.