If Excel won’t remove duplicates, select the complete data range, choose Data > Remove Duplicates, confirm which columns define a duplicate, and click OK. If records still seem to remain, the rows may differ in a selected column, contain invisible spaces, or be using a different duplicate rule than you expect.
What Remove Duplicates actually does
Excel’s Remove Duplicates command deletes repeated rows from the selected range. It keeps the first occurrence it finds and removes later rows that match it according to the columns checked in the dialog. The command changes the source data; it is not just a filter or a visual highlight.
The checked columns determine what counts as a match. If you check only an Email column, rows with the same email address count as duplicates even if their names or other details differ. If you check Name, Email, and Department, Excel removes a later row only when its entries match the first row across all three checked columns. A difference in even one selected field means Excel treats the rows as different.
Formatting alone usually does not make two records distinct. For example, a date displayed as “Jan 1” and the same date displayed as “1/1/2026” may have the same underlying value. Conversely, text that looks identical on screen can contain different spaces or characters. That is why the result can surprise you even when the rows appear alike.
Run the command on the right range
- Save a copy of the workbook or confirm that you can undo the change.
- Click a cell in the dataset. If Excel selects only part of the data, select the full rectangular range, including every column that belongs to each record.
- Choose Data > Remove Duplicates.
- If the first row contains column names, select My data has headers. Check the columns that should define a duplicate.
- Click OK and review Excel’s message reporting how many duplicate values were found and how many unique values remain.
If Excel asks whether to expand the selection, choose Expand the selection when each row is a complete record. Choosing to continue with the current selection can remove values from one column without keeping the rest of each row aligned. For example, deleting a repeated email from a single selected column could leave the names and phone numbers beside it unchanged, pairing records incorrectly.
When the data is formatted as an Excel table, click a cell in the table and use the same Data tab command. The table’s header row is normally recognized, but still check the header option and the column checklist before confirming.
Check which columns are selected
A frequent reason duplicates remain is that Excel is checking more columns than the reader intended. Imagine two rows with the same customer email but different order numbers. If Order Number is checked, the rows are not duplicates as complete records, so both stay. If your goal is one row per customer email, select only the email field as the duplicate criterion—but first decide which customer details should be kept from among the matching rows.
The opposite mistake is checking too few columns. If you remove duplicates using only Last Name, Excel may remove a different person who happens to share that surname. Before deleting anything, write down what “duplicate” means for the task: identical whole rows, repeated email addresses, repeated product IDs, or another key. Select exactly those identifying columns.
- One copy of each email: check Email only.
- One copy of each customer and email combination: check Customer and Email.
- Only identical records: check every column that forms part of the record.
Excel keeps the first matching row in the range’s current order. If you want to keep the newest transaction or the most complete record, sort the data so the preferred record comes first before removing duplicates. Check that the sort includes the entire dataset, not just one column.
Look for spaces and invisible characters
Leading spaces, trailing spaces, line breaks, and nonbreaking spaces can make two cells compare differently even when they look the same. A copied value from a website may contain a nonbreaking space, which is not always removed by ordinary cleanup. Imported data may also include control characters that are hard to see in the grid.
A helper column can standardize text before you run Remove Duplicates. With the original text in A2, enter this in B2 and fill it down:
Assuming the value to clean is in A2, this removes common nonprinting characters, replaces nonbreaking spaces with regular spaces, and trims leading and trailing spaces while reducing repeated internal spaces:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
For example, if A2 contains “ North ” with an extra space at either end, the helper result is “North”. TRIM also turns multiple ordinary spaces between words into one. Review the cleaned results before relying on them: that normalization may be undesirable when repeated spaces are meaningful in a particular field. After checking, use the helper column as the duplicate criterion, or copy its results and paste values if you want to replace the original text.
Do not assume that a cell’s displayed appearance reveals its full contents. Click the cell and inspect the formula bar; for more detailed checks, compare the source and cleaned helper values side by side. If the issue is inconsistent spelling, punctuation, or abbreviations rather than whitespace, decide on a standard form before removing records.
Consider data types and capitalization
Numbers stored as text, actual numbers, dates, and text that resembles a date can behave differently in comparisons and later calculations. If some IDs were imported as text and others entered as numbers, standardize the column before deduplication. For identifiers such as product codes, keep leading zeros if they are significant; converting “00125” to the number 125 would change the identifier.
Capitalization can also be a source of confusion. Excel’s Remove Duplicates is generally not a case-sensitive way to distinguish entries such as “ABC” and “abc”. If capitalization carries meaning in your data, do not use the command without checking a copy and confirming the behavior you need. You can create a deliberate case-sensitive comparison with formulas or Power Query, but the right method depends on how the records should be matched.
Also check for punctuation and Unicode lookalikes. A standard hyphen and a longer dash, or a regular space and a nonbreaking space, may look very similar but are different characters. Cleaning and standardizing imported text is often more reliable than repeatedly running Remove Duplicates on the raw values.
Use highlighting to diagnose before deleting
To see what Excel considers repeated before changing the data, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This is useful for spotting repeated entries in one selected column. It does not, by itself, identify duplicate whole rows across several fields, so use it as a diagnostic rather than proof that complete records match.
For full-row comparisons, temporarily sort by the key columns or add a helper key that combines the fields you care about. Be careful when combining values: simple concatenation can create ambiguous keys. For example, joining “AB” with “C” produces the same text as joining “A” with “BC” if no separator is used. A delimiter that cannot occur in the data, or a more structured comparison method, avoids that particular collision.
Filtering is another non-destructive way to inspect a list, but a filter that hides repeated values does not delete them. Clear filters before reviewing the final dataset, and avoid confusing a hidden row with a removed one.
Try a formula when you want to preserve the source
If you want a separate list of unique values rather than deleting records, Microsoft 365 includes the UNIQUE function. With the source list in A2:A100 and the formula entered in an empty cell such as C2, use:
=UNIQUE(A2:A100)
If A2:A4 contains North, South, North, the spilled result is North followed by South. The original cells remain untouched, and the output updates when the source data changes. UNIQUE is available in Microsoft 365 and Excel 2021 or later; it is not a substitute for removing duplicate complete rows unless you supply the appropriate range and understand what each returned row represents.
For a repeatable cleanup of large imported files, Power Query is another option. It can remove duplicates from chosen columns and record the transformation as a query step, making it easier to refresh the cleanup when new data arrives. The command is available from Data > Get Data or the workbook’s existing query tools, depending on how the data was imported. As with the ribbon command, choose the key columns deliberately and verify which row is retained.
If the command is unavailable or the result is wrong
If Remove Duplicates is disabled or does not behave as expected, check the workbook’s state and selection. Exit cell-edit mode by pressing Enter or Esc, click inside the intended range, and make sure the worksheet is not protected. A protected sheet can block edits; unprotect it only if you are authorized and have the required password. If the workbook is read-only, you may be able to make changes temporarily but may not be able to save them back to that file.
After the operation, Excel’s result message is a useful clue. If it reports zero duplicates, revisit the selected columns and inspect values for hidden characters or genuine differences. If it reports fewer removals than expected, the matching key may include extra columns. If it reports many removals, verify that you did not select a non-unique field such as a city or surname by itself.
Remove Duplicates is destructive in the sense that it removes rows from the current range, but you can usually reverse the most recent operation with Ctrl+Z on Windows or Command+Z on Mac. Undo may not be available after closing the workbook or after certain later operations, so a saved copy is safer for important data. Excel for Mac and Excel for the web may arrange ribbon controls differently, but the same principles apply: choose the range, identify the key columns, and inspect the outcome. If a menu label differs in your version, use Excel’s command search to locate Remove Duplicates.
Frequently Asked Questions
Why does Remove Duplicates say there are no duplicates?
Excel may be checking extra columns, or the apparent matches may contain spaces, different characters, or different underlying values. Inspect the selected columns and compare the cells’ actual contents, not only how they look.
Does Excel keep the first or last duplicate?
It keeps the first matching row in the range’s current order and removes later matches. Sort the complete dataset first if a particular record should be kept.
Can I remove duplicates based on just one column?
Yes. In the Remove Duplicates dialog, check only that column. Excel will retain the first row for each value and remove later rows with the same value, including their other fields.
Does Remove Duplicates delete the original data permanently?
It removes rows from the selected range, but the change can usually be undone immediately with Ctrl+Z on Windows or Command+Z on Mac. Save a backup first when the data matters.
How do I find duplicates without deleting them?
Use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values for a single field, or use a helper column, filter, or UNIQUE formula to inspect a separate result before changing the source.
Related
- Excel Sheet Protection: How to Protect and Unlock Cells
- Excel Remove Duplicates: Safe Steps and Alternatives
- Excel Very Slow or Not Responding? Causes and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Table Reference Syntax: A Practical GuideExcel table references use a table name and, usually, a column name in square brackets. For example, with…
- Excel Shortcut to Change Sheets in Windows and MacIn Excel for Microsoft 365 on Windows, press Ctrl+Page Down to move to the next worksheet tab, or…
- Temporary Filter in Google Sheets: How to Use OneTo apply a temporary filter in Google Sheets without changing what collaborators see, open the sheet in a…
- How to Set an Out of Office Reply in Outlook 365To set an out of office reply in Outlook 365, open Automatic Replies, switch them on, write your…
- How to Add a Drop-Down List in Excel: Step-by-Step GuideTo add a drop-down list in Excel for Microsoft 365 on Windows, select the cell or cells, choose…
- Box and Whisker Plot in Excel: Create and Read OneTo create a box and whisker plot in Excel for Microsoft 365 on Windows, put each group of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.