If Excel cannot remove duplicates, select a cell in the complete data range, choose Data > Remove Duplicates, confirm the columns that define a duplicate, and select OK. If duplicates remain, the usual cause is that Excel is comparing different columns than you expect, or the cells contain hidden differences such as extra spaces.
How Remove Duplicates works
Excel’s Remove Duplicates command checks the columns you select and deletes later rows whose selected values match an earlier row. It keeps the first occurrence in the current row order. For instance, if a list contains the same customer ID on rows 3 and 8, removing duplicates based only on the ID keeps row 3 and deletes row 8, even if other details in those rows differ.
The selected columns determine what counts as a match. If you select only Customer ID, rows with the same ID count as duplicates. If you select Customer ID, Date, and Amount, Excel removes a row only when the values in all three selected columns match a previous row. This distinction often explains why a command appears to have done nothing: a person may regard two records as duplicates based on one identifying field, while Excel is comparing several fields.
The command changes the data; it is not just a filter. Before using it, save a copy or make a backup of the sheet. You can usually undo the operation immediately with Ctrl+Z on Windows or Command+Z on Mac, but an undo may not be available after other actions or after closing the workbook.
Check the selected range and columns
Start by checking what Excel is about to process. Click inside the data rather than selecting just one column, then choose Data > Remove Duplicates. In the dialog, make sure the full set of fields is included and that My data has headers is selected when the first row contains headings. If headers are not recognized, Excel can treat the heading row as ordinary data.
If Excel asks whether to expand the selection, choose to expand it when the goal is to remove complete duplicate records. Choosing to continue with only the selected column can remove values from that column without keeping the rest of each row aligned with the original records. That can corrupt a table even though the duplicate count looks right.
- For identical records: select every column that makes up the record.
- For one unique field: select only the column that defines uniqueness, but consider whether deleting entire rows based on that field is appropriate.
- For a table with headings: check the header option and confirm the column names in the list.
For example, a sales table may contain repeated product names because the same product was sold on different dates. Removing duplicates based only on Product would discard legitimate sales rows. Selecting every field, on the other hand, removes only rows that repeat the same product, date, quantity, and other included values.
Look for spaces and invisible characters
Text that looks identical on screen may not be identical to Excel. A cell can contain a leading space, a trailing space, repeated spaces, a nonbreaking space copied from a website, or a control character. Thus, Northwind and Northwind may appear the same at a glance but compare as different values. Imported web pages and copied reports are frequent sources of this problem.
Inspect a few supposed duplicates by clicking each cell and looking in the formula bar. You can also widen the column or temporarily use a helper column to normalize the text. In Microsoft 365, with source text in A2:A100 and a helper column beginning in B2, enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in B2 and fill it down. For a value such as Northwind with a nonbreaking space at the end, the helper returns Northwind. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces; CLEAN removes certain control characters; SUBSTITUTE replaces the nonbreaking space represented by CHAR(160). It does not remove every possible Unicode character, so inspect unusual imported data when results still differ.
After checking the normalized values, you can copy the helper column and use Paste Special > Values if you intend to replace the original text. Keep the original column until you have confirmed the result. For recurring imports, cleaning the data in Power Query can be safer than repeating manual edits.
Check data types, formulas, and case
Numbers and text that look like numbers are not necessarily the same underlying value. For example, a cell containing the number 125 and a text value containing 125 can behave differently in comparisons and other operations. Dates can also arrive as text rather than as Excel date values. If data was imported, check whether values are aligned unexpectedly, use the formula bar, and inspect the number format. Formatting a text value as a number does not necessarily convert it into a numeric value.
Cells containing formulas are compared by their calculated results for this purpose, not by whether the formulas themselves have matching text. Two different formulas that return the same value can therefore be treated as duplicates when their selected results match. Conversely, if formulas recalculate or a connected source changes, the data may look different after a later refresh.
Excel’s Remove Duplicates comparison is not case-sensitive: text such as Maple and maple is generally treated as a duplicate when the other selected values match. If capitalization represents a meaningful distinction in your data, do not rely on the standard command alone. Create a separate, case-sensitive check or preserve a backup and verify the proposed matches before deleting rows.
Understand blanks, filters, and tables
Blank cells can complicate the result. If the chosen comparison columns contain blanks, rows may still be duplicates when the values across those selected columns match. But two records with a blank in one field are not necessarily duplicates if other selected fields differ. Decide what an empty field means in your records before removing anything; a missing customer ID, for example, may not safely identify two records as the same customer.
Filters and hidden rows affect what you see, but they are not a reliable way to define which records the command should remove. Clear filters or review the full range before cleanup so the result is easier to verify. If the data is an Excel table, click inside it and use the same Data tab command; the table’s column headers appear in the dialog. Check the result and the table row count afterward.
A protected worksheet can prevent changes. If the command is unavailable or Excel refuses to edit the range, check whether the sheet is protected and whether you have permission to unprotect it. Merged cells, an incomplete selection, or a workbook opened with restricted editing can also make cleanup awkward. Do not remove protection without authorization, especially in a shared or controlled workbook.
Remove duplicates safely in Microsoft 365
Use this workflow when you need to edit the existing list in Excel for Microsoft 365 on Windows:
- Save a copy of the workbook, or copy the source data to a separate sheet.
- Click a cell in the data and check that the range has a single header row and no unrelated information directly beside it.
- Choose Data > Remove Duplicates.
- Confirm whether the first row contains headers, then select the columns that define a duplicate.
- Select OK and read Excel’s summary of how many duplicate values were removed and how many unique values remain.
- Review the remaining records, especially when the key field can be blank or when rows contain important details.
On Excel for Mac, use the Data tab and choose Remove Duplicates; the dialog’s wording and layout can vary slightly. Excel for the web may also offer duplicate removal, but available controls can differ by account and release. If the command is missing, open the workbook in the desktop app rather than assuming that the data itself is the problem.
Choose a formula or Power Query when deletion is not ideal
Remove Duplicates permanently changes the current list, which is not always what you want. If you need a separate, automatically updating list of distinct values, Microsoft 365 and Excel 2021 or later support the UNIQUE function. With values in A2:A6—North, South, North, East, South—enter =UNIQUE(A2:A6) in an empty cell. The spilled result is North, South, East, in first-seen order; the source values remain untouched. Make sure the cells below the formula are clear so the result can spill.
That example returns unique values from one column. For a list of records, UNIQUE can return distinct rows when given a multi-column range, but it treats a whole row as the comparison unit. It does not automatically mean “keep one row for each customer ID while choosing the newest date.” That requires a deliberate rule for which record to retain, not merely duplicate removal.
For repeated imports or larger cleanup jobs, Power Query offers a repeatable process. Select the data and choose Data > From Table/Range to open the Power Query Editor. Select the columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates. Load the result back to Excel. When the source is updated, refresh the query and the transformation runs again. Power Query is useful when you want the source data preserved, but verify the selected columns and the retained rows; removing duplicates is still a choice about what counts as one record.
When duplicates return after cleanup
If rows reappear, determine whether the data is being regenerated. A formula, pasted addition, external connection, or Power Query refresh can bring repeated records back after a one-time cleanup. Remove Duplicates does not prevent future entries from being added, and it does not create a permanent uniqueness rule for the source.
For a connected or regularly imported list, clean the source or build duplicate removal into the refresh process. For a manually maintained list, use a consistent identifier and consider Data Validation or an appropriate lookup/checking process to flag repeated entries before they accumulate. A visual duplicate highlight can help find candidates, but it does not remove rows and may not use the same criteria as your intended business rule.
Finally, distinguish exact duplicates from legitimate repeated events. Two invoice lines can share a customer and product but refer to separate purchases; two people can share a name. Before deleting records, decide which combination of fields genuinely identifies a unique record and retain the information needed to explain the decision.
Frequently Asked Questions
Why does Excel say no duplicates were found?
Excel may be comparing more columns than you intended, or the text may contain extra spaces, nonbreaking spaces, or other hidden characters. Confirm the selected columns and inspect or normalize sample values.
Does Remove Duplicates keep the first or last row?
It keeps the first occurrence in the current order and removes later matching rows. Sort the data first if a particular row—such as the newest record—should be retained, and preserve a backup before changing the order.
Can I remove duplicates based on one column but keep the whole row?
Yes. In the Remove Duplicates dialog, select only the identifying column. Excel removes later matching rows from the data range, so use this only when one value truly determines which full records are redundant.
Why are duplicate values still highlighted after removal?
Conditional formatting may still mark values that meet its own rule, or the repeated values may be in a different range or column. Remove Duplicates removes matching rows based on its selected columns; it does not clear formatting rules or search every sheet in the workbook.
Will Excel remove duplicates automatically when I add new data?
No. The command performs a one-time cleanup. Use a formula such as UNIQUE for a separate dynamic list, or set up a Power Query process for data that is regularly refreshed.
Related
- Excel Text to Columns and Rows: How to Split Data
- Excel Text to Columns: How to Make Results Vertical
- Excel Table Structured References: Rules, Examples & Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Paste a Link in Excel as Plain Text, Not HyperlinkTo paste a URL into Excel as plain text, select the destination cell, press F2 to edit it,…
- COUNTIF Text in Google Sheets: Examples and TipsTo count text in Google Sheets, use COUNTIF with the cells to check and the text criterion, such…
- How to Search in Google SheetsTo search in Google Sheets, press Ctrl+F on Windows or Command+F on Mac, enter a word or number,…
- How to Add Mail Merge Fields in Word: Step-by-StepTo add mail merge fields in Word, connect a recipient list, place the cursor where personalized information should…
- Audit Working Papers Excel Templates ExplainedYes. Audit working paper Excel templates are available for organizing procedures, evidence, calculations, and review notes, but an…
- How to Insert a Background Image in an Excel WorksheetTo add a background image in Excel for Microsoft 365 on Windows, select the worksheet, open Page Layout…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.