Excel Not Removing Duplicates? Fix Common Causes

Errors & fixes · Updated November 18, 2026

If Excel is not removing duplicates, first check that you selected the full data range and the correct columns in Data > Remove Duplicates. The command removes rows that match across the columns you choose; hidden spaces, different underlying values, or a mistaken header setting can make entries that look alike count as different.

What Excel considers a duplicate

Remove Duplicates compares the values in the columns you select, not just what the worksheet appears to show. If you select several columns, Excel treats a row as a duplicate only when its values match the other row in every selected column. For example, two records with the same customer name but different order numbers are not duplicates if both columns are checked. If you select only the customer column, Excel can treat those records as duplicates.

The command keeps the first occurrence and removes later matching rows. It is not a tool for merging records or choosing the most complete version. Before using it, decide what makes a record genuinely redundant: an identical email address, a repeated invoice number, or a fully identical row are different rules and require different column selections.

Excel generally does not distinguish uppercase from lowercase when identifying duplicate text, so “MIRA” and “Mira” can be treated as duplicates. Formatting is not a reliable test either. A date displayed as 3/4/2026 might contain a time value that is not visible in the cell, and a number formatted with leading zeros may have a different stored value from text that includes those zeros.

Run Remove Duplicates without losing unrelated data

In Excel for Microsoft 365 on Windows, click a cell in the list or select the complete data range, then choose Data > Remove Duplicates. In the dialog, confirm whether the first row contains headers and check the columns that define a duplicate. Select all columns to remove only fully identical records; select a specific identifier column to keep just one row per identifier. Click OK and review the result and the message reporting how many duplicate values were removed.

  1. Save a copy of the workbook or make sure you can undo the change.
  2. Check that the range includes every column belonging to each record, with no unrelated data mixed into it.
  3. Choose the correct header option and duplicate-defining columns.
  4. Run the command, then inspect a few retained rows and the reported removal count.

Remove Duplicates changes the data; it does not merely hide repeated rows. Ctrl+Z undoes the action in Windows if it is still the latest change. On a Mac, use Command+Z. Undo may no longer be available after closing the workbook or performing actions that clear the undo history, so a backup is safer for important lists. The ribbon command is also available in current Excel for Mac, though the layout can vary. In Excel for the web, look for Remove Duplicates on the Data tab; if the command is unavailable in your view, open the workbook in the desktop app.

Check the selected range and columns

A frequent cause is selecting only part of a table. If Excel offers to expand the selection when you start the command, choose the option that includes the adjacent data when those columns belong to the same records. Keeping only the current selection can leave related values behind or put columns out of alignment, depending on what was selected. Do not include a nearby notes section or a second table just because it is adjacent.

Next, check the boxes in the Remove Duplicates dialog. If you intend to remove repeated email addresses, checking both Email and Signup Date means Excel will remove a row only when both values match. The same email with a different date will remain. To keep one row per email, select Email alone, but remember that Excel retains the first matching row in the range. Sort the data first if you need a particular record—such as the newest date—to be the one retained.

If the dialog says “My data has headers” when the first row is actually a record, Excel may exclude that row from comparison. If the first row contains column names but the option is unchecked, the headings themselves may be treated as data. Confirm the setting every time rather than assuming Excel has recognized the headers correctly.

Clean extra spaces and invisible characters

Two cells can look identical while one contains a leading space, a trailing space, a nonbreaking space copied from a webpage, or a control character. A font, narrow column, or cell alignment can make these differences hard to notice. Ordinary TRIM cleanup addresses many extra standard spaces, but Excel’s TRIM does not remove every special whitespace character. CLEAN removes many nonprinting control characters, while SUBSTITUTE can replace a nonbreaking space.

For example, with the original text in A2, enter this in B2 and fill it down to create a cleaned comparison column:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

If A2 contains “ Mira ” with ordinary extra spaces, the result is “Mira.” If it contains a nonbreaking space around Mira, the SUBSTITUTE step changes that character to an ordinary space before TRIM cleans the text. Run Remove Duplicates using the cleaned helper column, or use it to inspect and correct the original data. Keep the original column until you have verified the results; a cleanup formula can change meaningful spacing in some kinds of text.

For a quick diagnosis, compare suspect values in the formula bar, use LEN to check character counts, or temporarily apply a formula-based check. With cleaned comparison values in D2:D100 and D2 holding the current value, this formula returns how many times that cleaned value occurs in the range:

=COUNTIF($D$2:$D$100,D2)

If D2’s cleaned value appears three times, the formula returns 3. A result greater than 1 means there is at least one other matching value, but it does not remove anything. Copy the formula down to check other rows.

Check numbers, dates, and formula results

When apparent duplicates survive, look beyond the displayed text to the stored values. An identifier such as 00127 may be stored as text in one row and as the number 127 in another. Applying a number format that displays 127 as 00127 does not necessarily convert the underlying value into text. Before removing duplicates, decide whether leading zeros are significant. For product codes and account IDs, they often are; converting everything to numbers could destroy meaningful information.

Dates can cause a similar surprise. Excel stores dates as serial numbers, and a cell formatted to show only the date may also contain a time. Two cells can both display 3/4/2026 while one contains midnight and the other contains 9:30 a.m. If date and time should count as one day for your task, create a comparison column that removes the time component, after confirming the cells are real Excel dates. If the times matter, keep them in the comparison.

Cells containing formulas are compared by their current results, not by whether their formulas have the same wording. Two formulas can produce the same value, while the same formula copied down can produce different results. Check that calculation is current and that the displayed results are what you expect before removing rows. Also distinguish a genuinely empty cell from a formula returning an empty string; they may look blank but are not necessarily equivalent for every comparison or later workflow.

Consider filters, tables, and blank rows

A filter can make it seem as if Excel ignored duplicates because you are viewing only some records. Clear filters or inspect the full list before judging the outcome. Do not assume that a row hidden by a filter is outside the data operation. If you need to remove duplicates only from a particular subset, make a separate copy of that subset or use a method that explicitly limits the records being processed.

Excel Tables help keep headers and records together, but they do not define what counts as a duplicate. Click inside the table and verify the columns in the dialog just as you would for a normal range. Empty rows within or around a list can also disrupt the range Excel detects automatically. Select the intended range explicitly if the list is split by blank rows or surrounded by other content. A blank in one selected column does not make every row with a blank there identical when other selected columns differ.

After removing duplicates, verify that the remaining row count and key records make sense. If the results are wrong, undo immediately rather than trying to reconstruct deleted rows. For a shared or important workbook, work on a copy and retain a source-data sheet.

Highlight duplicates before deleting them

If you are unsure which rows will be removed, identify them first. Select the relevant cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This applies visual highlighting; it does not delete data. The rule is useful for spotting repeated values in one column, but highlighting a repeated email does not tell you whether entire records are duplicates across several fields. Review the related columns before deciding which row to keep.

For a more controlled review, add a helper column that labels repeated keys, filter that column, and inspect the matching records. For example, the COUNTIF formula above can identify values occurring more than once. You can then compare dates, names, or status fields and decide whether the records are true duplicates, legitimate repeat transactions, or conflicting versions that need manual review.

When to use formulas or Power Query instead

Remove Duplicates is suited to a one-time cleanup where deleting later matches is the intended result. If you want a separate list that updates when source data changes, Excel for Microsoft 365 and Excel 2021 or later support the UNIQUE function. With source records in A2:C100, =UNIQUE(A2:C100) returns distinct rows as a spilled result without deleting the source. It does not automatically normalize spaces or decide which of two conflicting records should win, so clean or transform the data first when needed.

Power Query is useful for repeatable imports or recurring cleanup. In Excel, select the source data and choose Data > From Table/Range. In the Power Query Editor, select the columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates. Use Close & Load to return the result to Excel. The query preserves the original source and repeats the transformation when refreshed, but a later refresh can bring back duplicates if the source still contains them; the removal step must remain in the query. Be deliberate about the selected columns, because the same matching rule applies here.

Frequently Asked Questions

Why does Remove Duplicates say zero duplicates were found?
Usually, the selected columns do not match exactly under Excel’s comparison, or the selection is wrong. Check header settings, extra spaces and special characters, hidden time values, and whether you selected too many columns. A helper column can make text differences easier to diagnose.

Does Excel keep the first or last duplicate?
It keeps the first occurrence in the current range and removes later matches. Sort the records before running the command if a specific row should be retained, and verify the sort order and headers first.

Can I remove duplicates from just one column?
Yes. In the dialog, check only that column. Excel uses it to decide which records match, but removes the corresponding duplicate rows from the selected data range. Include the full record range so the remaining columns stay aligned.

Does Remove Duplicates delete data permanently?
It removes rows from the current data, but Ctrl+Z on Windows or Command+Z on Mac can undo the latest action while it remains in the undo history. Save a copy before cleaning valuable data rather than relying only on undo.

How can I remove duplicates without changing the source list?
Use a separate output area with UNIQUE in Excel 2021 or Microsoft 365, or create a Power Query transformation and load its result separately. Both approaches leave the original source data available for comparison.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.