Remove Duplicates in Excel
Open an Excel workbook, choose matching columns and rules, keep the first or last occurrence, and download a clean copy without uploading the file.
Drop your file here or choose a fileTap to choose a file
Opened on your device - never uploaded · up to 50MB
Supports .xlsx, .xls, .xlsm, .xlsb · or paste a file from your clipboard
How to Remove Duplicates in Excel
Excel has three built-in ways to remove duplicate rows. The Remove Duplicates command deletes them in place, the UNIQUE function builds a de-duplicated copy that updates itself, and Advanced Filter copies the unique rows elsewhere in any version of Excel. The tool above does the same job without Excel and shows exactly which rows it removed.
Method 1: The Remove Duplicates command
Best for: A one-off cleanup in any version from Excel 2007 on, Windows or Mac.
- Click any cell inside the data, or select the exact range to check.
- Go to Data > Data Tools > Remove Duplicates.
- Leave My data has headers ticked if row 1 holds column names, so the header is never treated as data.
- Tick only the columns that define a duplicate. Tick Email alone to remove repeated emails even when other columns differ; leave every column ticked to remove only fully identical rows.
- Click OK. Excel reports how many duplicate values it removed and how many unique values remain. Press Ctrl+Z straight away if the count looks wrong.
Excel keeps the first row of each duplicate group and deletes the rest. To keep the latest record instead, sort newest-first before you run it.
Method 2: The UNIQUE function
Best for: Microsoft 365 and Excel 2021 or later, when the source must stay untouched.
- Click an empty cell with room below and to the right for the results.
- Type
=UNIQUE(A2:C500)and press Enter. The distinct rows spill into the cells below. - To list values that appear exactly once - dropping every copy of a repeated value rather than keeping one - use
=UNIQUE(A2:A500,,TRUE). - The result recalculates when the source changes. To freeze it, copy the spill range and use Paste Special > Values.
Method 3: Advanced Filter
Best for: Older versions, or keeping the original list next to the cleaned one.
- Select the data including its header row.
- Go to Data > Sort & Filter > Advanced.
- Choose Copy to another location and pick a destination cell in the Copy to box.
- Tick Unique records only and click OK.
In Google Sheets
Select the range and use Data > Data cleanup > Remove duplicates, or write =UNIQUE(A2:C500), which works the same way as in Excel.
Common mistakes
- Spaces make values different. "Maya" and "Maya " with a trailing space are not duplicates to Excel. Run
=TRIM(A2)in a helper column first, or use the tool above, which ignores surrounding spaces by default. - Case is ignored. Remove Duplicates and UNIQUE both treat "MAYA@EXAMPLE.COM" and "maya@example.com" as the same value.
- Displayed values are compared. Microsoft documents that duplicates are judged on what the cell shows, so the same date formatted two different ways can survive as two rows. Give the column one number format before you dedupe.
- Check before you delete. Highlight repeats first with Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values or the Find Duplicates tool, so you see what will go.
How a Duplicate Is Decided
Every data row is reduced to a key. If you tick no match columns the key is built from all of the row's cells; if you tick some, the key is built from only those, so two rows can differ everywhere else and still be duplicates. Each cell is converted to text, its surrounding spaces are removed unless you say otherwise, and its letters are lowercased unless you turn case sensitivity on. Rows with equal keys form a group, and all but one member of each group is deleted.
| Customer ID | Plan | Match on Email, Keep last | |
|---|---|---|---|
| C-1042 | maya@example.com | Basic | removed |
| C-2048 | grace@example.com | Pro | kept |
| C-1042 | Maya@Example.com | Pro | kept |
The first and third rows share an email once case is ignored, so they are one group; Keep last retains the later row and its Pro plan. Switching to Keep first would have retained the Basic row instead. Either way the two surviving rows come out in sheet order.
Keep First or Keep Last
This is the only decision that changes which data you end up with, and it is worth being deliberate about. Keep first is the safe default and matches what Excel's own Data > Remove Duplicates does. Keep last is what you want when rows arrive in chronological order and later rows are corrections - the newest address, the latest status. Neither option merges the rows: the values in the discarded row are gone, so if each duplicate holds different fragments of a record, deduplicate on a narrower key and reconcile the survivors by hand.
Matching Runs on Cleaned Values
Duplicate removal is the last step of the cleanup pass, so it sees whatever the earlier rules produced. Trimming and the Collapse repeated spaces rule run first; if a Text case transformation is set, it has already normalized the letters before any case-sensitive comparison happens - meaning UPPERCASE plus Match uppercase and lowercase separately will never find a case difference to distinguish. To make padding significant you have to untick both Trim leading/trailing spaces at the top and Ignore spaces at the start and end in the duplicate rules. Fill blank cells also runs first, so filling blanks with N/A makes every previously-blank cell in a match column collide.
Reading the Evidence Panel
After Apply Cleanup the report states how many rows were retained and removed, how many of the removals were duplicates, and - in the panel below - whether matching used the whole row or the specific columns you picked, naming them. That panel is the check to make before you download: if it says whole-row when you meant to match on Email, the tick simply did not register. The preview shows the first 30 surviving rows, and the download is a new workbook - your original file is never modified.
When a Different Tool Fits Better
Deleting rows is irreversible in the downloaded copy, so if you are not yet sure what is duplicated, start with Find Duplicates in Excel, which marks and counts every occurrence without removing anything. To know how many distinct records you should end up with, use Count Unique Values. When the duplicates are across two lists rather than inside one, Compare Two Columns is the right shape, and if the duplicates came from several files, merge them with Excel Merge first and dedupe once at the end.
Frequently Asked Questions
Yes. Tick the column or columns that define a duplicate - for example Email - and differences in the unticked columns will not stop two rows matching. Leave every match column unticked and the whole row has to be identical.
No. Keep last decides which row of each matching group survives, not where it sits. The surviving rows stay in the order they had in the sheet, so a Keep last dedupe of a chronological export gives you the newest record for each key with the file order intact.
Comparison runs on cleaned values. By default surrounding spaces are ignored and letters are compared case-insensitively, so 'maya@example.com' and ' Maya@Example.com ' are the same key. Tick Match uppercase and lowercase separately to make case count, and untick both the trim rule and Ignore spaces at the start and end to make padding count.
Yes. Every cell is converted to text before the keys are compared, so a numeric ID and the same ID stored as text match each other. That is usually what you want when merging exports from two systems, but it means a cell formatted as currency and one holding plain digits can also collide.
A blank becomes an empty key part, so two rows that are both blank in the match column count as duplicates of each other. If those rows are distinct records, add a second match column that is always populated, or fill the blanks before deduplicating.