Remove Duplicates in Excel Without Losing Data
By Mark Fulton · 2026-08-26 · 11 min read
Select your data, go to Data > Remove Duplicates, tick only the columns that define a duplicate, and click OK. That is the entire command and it runs in about four seconds. What costs people is everything the dialog does not say: Excel permanently deletes every matching row after the first one, decides what matches using an exact comparison of what is displayed in the columns you ticked, and keeps no record of what it removed. A trailing space, a date shown two different ways, or one column you forgot to untick will each change the answer. So the useful order is clean the values first, decide what makes a row unique second, review the matches third, and click the button last.
Most instructions for this feature stop at the four seconds. The four seconds are not the risky part.
What counts as a duplicate to Excel?
Remove Duplicates works on whole rows, but it only looks at the columns you tick in the dialog. Two rows are duplicates if the values in every ticked column match. Everything in the unticked columns is ignored for the comparison and deleted along with the row.
Three behaviours are worth knowing before you rely on it.
It keeps the first one. Microsoft's documentation is explicit: when duplicates are removed, the first occurrence of the value in the list is kept and other identical values are deleted. That means row order is a decision, not an accident. If you want the newest record of each customer, sort by date descending before you run the command, not after.
It compares what is displayed, not what is stored. Microsoft's guide to filtering for unique values gives the classic example: the same date value in two cells, one formatted as 3/8/2006 and the other as Mar 8, 2006, counts as two unique values. Same underlying serial number, two different strings on screen, no match. The reverse trap is just as common with IDs, where a leading apostrophe or a text-formatted column makes 00412 and 412 look related and compare as unrelated.
It will refuse on structured data. The same page states you cannot remove duplicate values from data that is outlined or that has subtotals. Clear those first.
The ribbon path is stable across platforms. On Windows and on Mac it is the Data tab, Data Tools group, Remove Duplicates. In Excel for the web it is Data > Remove Duplicates with the same column checkboxes. The dialog is the same three decisions everywhere: what range, does it have headers, which columns.
Why do trailing spaces hide duplicates?
Because "North " and "North" are different strings, and nothing on screen tells you which one you are looking at.
Stray whitespace arrives in almost every file that came out of another system. CSV exports pad fields. Text pasted from a web page or a PDF brings non-breaking spaces with it. People type a space before they tab out of a cell. The result is a column that looks perfectly consistent and behaves as if it holds two or three separate values, which is also why exact-match lookups miss rows that are visibly sitting right there.
For Remove Duplicates, whitespace fails in the direction people notice least. It does not delete rows you wanted to keep. It quietly leaves duplicates in place, you see the "0 duplicate values found" message, and you conclude the file was clean.
The fix in Excel is TRIM, which Microsoft describes as removing all spaces from text except single spaces between words. Note the documented limitation on the same page: TRIM was designed for the 7-bit ASCII space, character 32, and does not touch the Unicode non-breaking space, character 160, which is exactly the one that arrives from web and PDF copies. For those you need a substitution first:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
Then convert the helper column to values and put it back over the original before you run Remove Duplicates. The command compares cells in place, so a column of formulas beside your data does not clean the data, it just sits next to it.
Which columns should define uniqueness?
This is the decision the dialog asks you to make and never explains.
Ticking every column means "delete rows that are identical in every single field". That is the safe default and it is almost never what people actually want. It only catches literal export duplication, where the same record was written twice.
Ticking one column means "leave one row per value in this column". That is powerful and destructive in the same move, because the values in every other column of the losing rows go with them.
Take an order export with Order ID, Customer, Order Date and Amount:
| Ticked columns | What survives | Is that what you meant? |
|---|---|---|
| All four | Only byte-identical repeated rows are removed | Usually yes, and usually finds little |
| Order ID | One row per order | Usually the right answer |
| Customer | One order per customer, the rest deleted | Almost never |
| Customer + Order Date | One order per customer per day | Only if same-day orders are genuinely impossible |
The working rule: your key is the smallest set of columns that should never repeat in a correct version of this sheet. Write that sentence out for your file before you open the dialog. If you cannot finish it, you are not ready to delete anything.
What should you check before you click Remove Duplicates?
Five checks. Each one prevents a specific, recoverable-only-if-you-notice failure.
| # | Check | What it prevents |
|---|---|---|
| 1 | Copy the range to a second sheet or workbook | Unrecoverable loss once you save and close, since the deletion is permanent and Microsoft's own advice is to copy first |
| 2 | Trim leading and trailing spaces, including character 160 | Real duplicates surviving because one value carries an invisible space |
| 3 | Make dates and IDs display consistently | Identical records counting as unique because one shows Mar 8 and the other shows 3/8 |
| 4 | Clear outlines, subtotals and active filters | The command refusing to run, or running against a range you cannot see all of |
| 5 | Highlight the duplicates and count them first | Deleting 400 rows when you expected 40, and only noticing next week |
Checks 2 and 3 are the two that make Remove Duplicates return a wrong answer rather than a scary one, and they are the two nobody does. If you want the full pre-send version of this for the whole file rather than just the duplicate question, that lives in the messy spreadsheet checklist.
How do you review duplicates before deleting anything?
Look at the matches while they are still there. Three ways, in increasing order of how much they tell you.
Highlight them. Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This is per-cell, so it flags repeated values inside the selected column, not repeated rows. It is the fastest sanity check on a single key column and it costs nothing. If you want the wider view of which conditional formats are worth keeping in a file someone else reads, that is covered separately.
Build a key column, then highlight that. To review duplicate rows rather than duplicate values, concatenate your key columns into one helper column and highlight duplicates on it:
=TEXTJOIN("|", TRUE, A2, C2, D2)
Now the highlight is answering the same question the dialog will answer, and you can filter by colour and actually read the rows before any of them disappear.
Spill the unique rows somewhere else. The UNIQUE function returns distinct rows into a blank area without touching your source data, and its third argument, exactly_once, set to TRUE, returns only the rows that appear exactly once. Two formulas, and you can see both the deduplicated result and the set of records that were involved in a collision, before you commit.
Whichever you use, treat the message Excel shows after the deletion as a receipt. It tells you how many duplicate values were removed and how many unique values remain. If that number does not match what you counted, undo immediately.
What should you do instead when rows differ in one column?
Stop, because those rows are not duplicates.
Two rows sharing a customer ID but carrying different dates or different amounts are two events, not one event recorded twice. Remove Duplicates on the customer column will delete one of them and will not tell you which fields it threw away. That is the single most expensive misuse of this feature.
Three better moves:
Sort first, then dedupe. Since the first occurrence survives, sorting is how you choose the winner. Sort by date descending to keep the most recent record per key, ascending to keep the original. This is the whole technique, and it works because of a documented behaviour rather than a lucky one.
Use Power Query when the file will arrive again. In the Power Query Editor, Home > Remove Rows > Remove Duplicates works on the columns you select and is documented by Microsoft as basing the result on that selection. It keeps the entire row, leaves your source untouched, and reruns on next month's export without you rebuilding anything.
Aggregate instead of deleting. If the reason you want one row per customer is a total, you want SUMIFS or a PivotTable, not a deletion. Nothing is destroyed and the underlying records stay auditable.
When the two rows genuinely conflict, same key and contradictory values, that is a business decision about which source is right. No button resolves it.
Does Google Sheets behave the same way?
The command exists and the menu is different: Data > Data cleanup > Remove duplicates, then pick the columns and say whether the data has a header row. Sheets also ships the cleanup step Excel makes you write a formula for, at Data > Data cleanup > Trim whitespace.
The comparison rule is where they diverge, and it matters. Google's documentation states that cells with identical values but different letter cases, formatting, or formulas are considered to be duplicates. Excel's documentation says the comparison depends on what appears in the cell rather than the stored value, which is why two differently formatted dates stay unique there.
Read those two sentences together. The same file, the same key column, the same command in name, and a date column formatted inconsistently will collapse in Sheets and survive in Excel. If your workflow moves files between the two, do the deduplication in one of them and stick to it.
Frequently asked questions
Does Remove Duplicates keep the first or last row?
The first. Microsoft states that when duplicates are removed the first occurrence in the list is kept and other identical values are deleted. There is no setting for last. To keep the newest record, sort the range by date descending before you run the command, so the row you want to survive is the one that appears first.
Is Remove Duplicates case sensitive?
Microsoft's support pages do not state it either way, so do not take anyone's word for it including mine. Test it on a copy of your own file with two rows that differ only in case and see which behaviour you get in your version. If the answer matters to your result, take the guessing out entirely: add a helper column of =UPPER(A2), convert it to values, and dedupe on that so you have decided the rule instead of inheriting it. Google, for its part, documents that Sheets treats different letter cases as duplicates.
Can I undo Remove Duplicates?
Ctrl+Z on Windows or Cmd+Z on Mac reverses it immediately, in the same editing session. Once you save, close and reopen the workbook, the undo history is gone and the rows are not coming back. That is why Microsoft's guidance is to copy the original range to another sheet or workbook before you start, and why check 1 in the list above is check 1.
How do I remove duplicates across two columns only?
Select the full data range, not just the two columns, then untick everything except those two in the dialog. Selecting the whole range keeps each row intact as a unit. If you select a partial range, Excel prompts you about expanding the selection, and expanding is nearly always the right choice. Continuing with a narrow selection removes cells from those columns while the rest of the row stays put, which shifts your data out of alignment in a way that is genuinely hard to spot.
Clean the values before you compare them
Two of the five pre-flight checks are just "make the values in this file say what they look like they say". That is what SuperSheet's spreadsheet cleaner does. Drop an .xlsx or .csv on it and you get a plan before anything changes: how many cells carry leading or trailing spaces, which columns hold numbers Excel is treating as text, listed by name, and which headers would be re-cased. You approve the list, or you read it and close the tab having learned what is wrong with the file.
Two honest limits. It reports the whitespace as a total count of affected cells, not broken out by column, so it tells you how much there is rather than exactly where. And it does not remove duplicate rows at all, the same way it deliberately does not unmerge cells or split two tables sharing a tab. Those need a decision about your data that a tool guessing on your behalf gets wrong.
What it does is hand Remove Duplicates a file where the comparison means something. Try it on your own sheet. Nothing uploads, and the parsing and the download both happen in your browser.