Duplicate rows are one of the most common spreadsheet headaches. Whether you imported a customer list, merged two reports, or just pasted data twice by accident, Excel gives you several ways to clean things up. Here are four reliable methods, from the built-in one-click tool to a quick online option for plain-text lists.
Method 1: Use Excel’s Built-In “Remove Duplicates” Button
This is the fastest option for most cases, and it works in Excel 2007 and later.
- Select the range of cells (or a single cell inside your data table).
- Go to the Data tab on the ribbon.
- Click Remove Duplicates.
- In the dialog box, tick the columns you want Excel to compare. Leave all columns ticked to remove only fully identical rows.
- If your data has a header row, make sure “My data has headers” is checked, then click OK.
Excel will tell you how many duplicate values were found and removed, and how many unique values remain. Note that this keeps the first occurrence of each value and deletes the later copies — and the deletion is permanent, so work on a copy of your data if you are unsure.
Method 2: Use the UNIQUE Function (Excel 365 / Excel 2021+)
If you want a deduplicated list without touching your original data, the UNIQUE function is ideal. It spills the unique values into neighboring cells automatically.
=UNIQUE(A2:A100)This returns every distinct value from A2:A100, first occurrences in their original order. To dedupe across multiple columns at once, pass the whole range:
=UNIQUE(A2:C100)Because it is a formula, the result updates itself whenever your source data changes — handy for dashboards and recurring reports.
Method 3: Advanced Filter for a Unique Copy
On older Excel versions without UNIQUE, Advanced Filter does the same job:
- Select your data range.
- Go to Data → Advanced (in the Sort & Filter group).
- Choose “Copy to another location”, tick “Unique records only”, set a destination cell, and click OK.
You get a clean, deduplicated copy while the original stays untouched.
Method 4: Dedupe a Plain-Text List Online (No Excel Needed)
Sometimes your “spreadsheet” is really just a copied column, a CSV export, or a list of emails in a text file. For that, opening Excel is overkill. Paste the list into Trencada’s free Remove Duplicate Lines tool, and it strips the repeated lines instantly — right in your browser, no signup, no upload. You can then paste the clean list straight back into Excel.
Which Method Should You Use?
- Quick cleanup of a worksheet: the built-in Remove Duplicates button (Method 1).
- Live, self-updating unique list: the UNIQUE function (Method 2).
- Older Excel, keep the original intact: Advanced Filter (Method 3).
- Plain-text or CSV lists: the online dedupe tool (Method 4).
Frequently Asked Questions
Does Remove Duplicates keep the first or the last occurrence?
It keeps the first occurrence and deletes the later duplicates.
Is the removal case-sensitive?
No — Excel treats “Apple” and “apple” as duplicates. If case matters, use a formula-based approach instead.
Can I undo it?
Yes, immediately with Ctrl+Z. But once the file is saved and closed, the deleted rows are gone — so keep a backup copy of important data.
Clean data makes every spreadsheet task easier. Pick the method that fits your situation, and your lists will be duplicate-free in under a minute.
