SpreadsheetFix

Excel’s Remove Duplicates isn’t removing duplicates: why, and what to do

You select the data, click Data > Remove Duplicates, and Excel reports that it found no duplicate values, or removed fewer than you can count with your own eyes. The rows really do look the same. In almost every case they aren’t the same to Excel: one has a character you can’t see, a value is stored or formatted differently, or the dialog is comparing more columns than you meant. This guide walks through the four usual causes, how to prove which one you have, and how to clean the file so duplicates match.

Updated
Applies to
Excel, Google Sheets, CSV
SpreadsheetFix rule
Free up to 5,000 rows

Why this happens

Remove Duplicates compares cell contents character by character. “Acme Ltd” and “Acme Ltd ” with a trailing space are different strings, and so are two names where one has a double space in the middle. Exports from accounting and CRM systems are full of padded fields, and none of it shows in the grid, so the rows look identical while Excel sees two different customers.

Non-breaking spaces are the stubborn version of the same problem. They arrive when text is copied from a web page, a PDF or some banking portals, and they look exactly like normal spaces but are character 160 rather than 32. Excel’s TRIM function leaves them alone, which is why cleaning a column with TRIM and running Remove Duplicates again often changes nothing.

Formatting can split values that are really the same. Microsoft’s documentation says duplicates are judged on the value displayed in the cell, so the same date shown as 08/03/2024 in one row and 8 Mar 2024 in another counts as two values. Numbers can split too: 1200 stored as a number and “1200” stored as text, usually marked by a small green triangle in the corner of the cell, are not treated as the same.

Finally, check what the dialog is comparing. By default every column in the selection is ticked, so two rows for the same invoice with different import timestamps or row IDs are not duplicates by Excel’s definition. Untick everything except the columns that identify a record, and the result changes completely.

Fix it by hand

Find out what differs

  1. 1

    Pick two rows that should be duplicates and compare a cell from each directly, for example =A2=A3. FALSE means the values differ even though they look alike.

  2. 2

    Compare their lengths with =LEN(A2) and =LEN(A3). A longer value is carrying extra characters, usually spaces at the end.

  3. 3

    Check the last character with =CODE(RIGHT(A2,1)): 32 is a normal space and 160 is a non-breaking space that TRIM won’t remove.

  4. 4

    For numbers and dates, test the type with =ISNUMBER(A2). TRUE in one row and FALSE in the other means one of them is stored as text.

Clean the columns, then remove duplicates

  1. 1

    Add a helper column with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to swap non-breaking spaces for normal ones and then strip and collapse the spaces, and fill it down.

  2. 2

    Copy the helper column and use Paste Special > Values over the original column, then delete the helper. Repeat for each text column that identifies a record.

  3. 3

    Fix numbers stored as text by selecting the column and choosing Convert to Number from the warning icon, and give date columns a single number format.

  4. 4

    Select the data, choose Data > Remove Duplicates, tick only the columns that identify a record, and click OK. Excel keeps the first row of each group.

Keep a copy of the sheet before you run Remove Duplicates. Excel tells you how many rows it removed but not which ones, so the copy is the only way to check its work.

In Google Sheets

  1. 1

    Select the data and use Data > Data cleanup > Trim whitespace to strip spaces from the start and end of each cell.

  2. 2

    Then use Data > Data cleanup > Remove duplicates, choose the columns to compare, and tick Data has header row if your first row holds column names.

  3. 3

    Check a few of the remaining rows with =LEN() and =CODE() if duplicates still survive; a character Trim whitespace didn’t touch is the usual reason.

Fix it with SpreadsheetFix

SpreadsheetFix fixes the spacing before it compares anything. The Trim Whitespace rule removes whitespace from both ends of every text cell and collapses runs inside it to a single space, and it treats non-breaking spaces, tabs and line breaks as whitespace too. Remove Duplicates runs after it, so “Acme Ltd”, “Acme Ltd ” and “Acme Ltd” have already become the same value by the time rows are compared.

By default a row is removed only when every cell matches an earlier row, and the first occurrence is kept. If records should be matched on a key, such as an email address or an invoice number, use the column picker on Remove Duplicates to compare only those columns. Unlike Excel, the preview lists every row that will be removed before you download anything, and the totals check shows how much each amount column lost, so a removal that takes out real money is easy to spot.

Trim Whitespace and Remove Duplicates are both free rules for files up to 5,000 rows, and the file is read in your browser rather than uploaded.

Choose a fileor drop a CSV, TSV or Excel file here, up to 50 MBProcessed in your browser. Nothing is uploaded.

No file to hand?

The same cleaner is on the Remove Duplicates page.

Before and after

Before
customer,email,amount
Acme Ltd,ap@acme.io,120.00
"Acme Ltd ",ap@acme.io,120.00
"Acme  Ltd",ap@acme.io,120.00
Contoso GmbH,billing@contoso.de,89.50
After
customer,email,amount
Acme Ltd,ap@acme.io,120.00
Contoso GmbH,billing@contoso.de,89.50

The second Acme row ends in a non-breaking space and the third has a double space. Once both are trimmed, all three rows match and two are removed.

Totals check, amount: 449.50 − 240.00 removed = 209.50

What it won’t catch

  • Matching is exact after spacing is fixed, and it is case-sensitive: “ACME Ltd” and “Acme Ltd” stay as two rows, and so do “Acme Ltd” and “Acme Limited”. Excel’s Remove Duplicates ignores capitalisation, so it may remove rows that SpreadsheetFix keeps.
  • Values are compared as they are written in the file, so 1200 and 1200.00 in the same column are different. Dates in mixed formats match only if Normalize Dates is switched on too, because it rewrites them to one format before duplicates are checked.
  • A file saved from Excel after a failed attempt may already have lost the evidence, for example leading zeros on IDs. Clean the original export where you can.

Questions

Why is Excel not removing duplicates?

Because the rows aren’t identical to Excel. The usual culprits are trailing or non-breaking spaces, the same value formatted or stored differently, or extra columns ticked in the Remove Duplicates dialog that differ between the rows.

Why doesn’t TRIM make the duplicates match?

TRIM removes only the normal space character. Non-breaking spaces from web pages and PDFs survive it, so replace them first with SUBSTITUTE(A2,CHAR(160)," ") and trim the result.

How do I remove duplicates based on one column?

In Excel’s Remove Duplicates dialog, untick every column except the one that identifies a record, such as email. In SpreadsheetFix, pick that column in the Remove Duplicates column picker.

Which row is kept when duplicates are removed?

Both Excel and SpreadsheetFix keep the first occurrence and remove the later ones, so sort the data first if you want a particular copy, such as the most recent, to survive.