How to deduplicate a customer file in Excel or Google Sheets, without AI

Why doesn't Remove Duplicates find anything between two files?#

Excel's Remove Duplicates button only removes rows that are strictly identical. But between your CRM and a file you receive, the same business is almost always written differently:

It is the same auto repair shop, but no cell matches. Across the 1,513 received rows in our test, with name, postal code and phone number as they were, the button flagged none. Once the columns are put in the same format, everything changes:

The button also deletes rows for good: Microsoft advises copying the original data first. Here, you mark rows and delete nothing. Why a single clue is often misleading: see the guide with Claude.

Are the formulas enough for your CRM?#

It all depends on the phone number. To see where you stand, compare in the CRM tab =COUNTA(C:C) (rows with a phone number) with =COUNTA(A:A) (all rows). Out of 100 rows already in the CRM, here is how many our formulas find:

Use the guide with Claude too if names change a lot from one file to the other, or if your file has many chain locations sharing one number: the "to check" bucket then grows too large to sort by hand.

Step 1: export both files and prepare the columns#

Export the companies from your CRM as a CSV or Excel file, then put both files in one workbook: a "CRM" tab and a "Received" tab. In Google Sheets, import each file with File > Import and uncheck "Convert text to numbers, dates, and formulas": otherwise a number written "+33 2 43…" can be read as a formula and shows an error, as in our test. In both tabs, put the name in column A, the postal code in B and the phone number in C. Then add columns D through H below and fill each formula down. The formulas are written for row 2, the first data row.

Column D, the phone number without spaces or dots:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ",""),".",""),"-",""),"/",""),"+33","0")

Column E, the 10-digit phone number (the French format):

=IF(LEFT(D2,4)="0033",REPLACE(D2,1,4,"0"),IF(AND(LEN(D2)=11,LEFT(D2,2)="33"),REPLACE(D2,1,2,"0"),IF(LEN(D2)=9,"0"&D2,D2)))

This formula fixes two traps. When a file is opened, the spreadsheet often turns a number written without spaces, such as 0299000001, into a numeric value and drops the leading 0: only 9 digits are left. It also turns +33299000001 into 33299000001: 11 digits.

Column F, the name without its legal form (SARL, SASU, SAS, EURL, SNC, SA and EI are French company legal forms):

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&UPPER(A2)&" "," SARL "," ")," SASU "," ")," SAS "," ")," EURL "," ")," SNC "," ")," SA "," ")," EI "," "))

Column G, the name + postal code key:

=IF(F2="","",F2&"|"&IF(LEN(B2)=4,"0"&B2,B2))

Column H, the phone + postal code key:

=IF(E2="","",E2&"|"&IF(LEN(B2)=4,"0"&B2,B2))

A postal code such as 01000, which became 1000 when the file was opened, gets its leading 0 back. Here is what these columns give on three made-up rows:

Google Sheets uses the same English function names (list of Google Sheets functions). We tried them in Google Sheets on made-up rows; we did not try them in Microsoft Excel, whose documentation describes the same functions.

If you have the SIRET number (the French ID of a business location) on both sides, paste it into column J of each tab and remove its spaces with Find and Replace (Ctrl+H): it must have 14 digits.

Step 2: mark the rows already present#

In the Received tab, add the status formula in column I, then fill it down:

=IF(AND(E2<>"",COUNTIF(CRM!H:H,H2)>0,COUNTIF(E:E,E2)=1,COUNTIF(CRM!E:E,E2)=1),"already",IF(AND(G2<>"",COUNTIF(CRM!G:G,G2)>0),"already",IF(AND(E2<>"",COUNTIF(CRM!E:E,E2)>0),"to check","new")))

It sorts each received row into one of three buckets:

• "already": the same phone number, appearing only once in each file, with the same postal code; or the same name with the same postal code.
• "to check": the same phone number, but shared by several rows or tied to another postal code.
• "new": everything else.

If you added the SIRET number in column J, put it at the start of the status formula, which becomes:

=IF(AND(J2<>"",COUNTIF(CRM!J:J,J2)>0),"already",IF(AND(E2<>"",COUNTIF(CRM!H:H,H2)>0,COUNTIF(E:E,E2)=1,COUNTIF(CRM!E:E,E2)=1),"already",IF(AND(G2<>"",COUNTIF(CRM!G:G,G2)>0),"already",IF(AND(E2<>"",COUNTIF(CRM!E:E,E2)>0),"to check","new"))))

Our test CRMs had no SIRET numbers: this rule is not measured here.

To spot the buckets at a glance, color them with Home > Conditional Formatting, as Microsoft's help explains. To pull the matching CRM record's ID into column K, use XLOOKUP, where X is the ID column in the CRM tab. The formula looks up the phone + postal code key first, then the name + postal code key:

=IF(I2<>"already","",IFERROR(XLOOKUP(IF(H2="","-",H2),CRM!H:H,CRM!X:X),XLOOKUP(G2,CRM!G:G,CRM!X:X,"")))

XLOOKUP does not exist in Excel 2016 and 2019, according to Microsoft. Use this one instead:

=IF(I2<>"already","",IFERROR(INDEX(CRM!X:X,MATCH(IF(H2="","-",H2),CRM!H:H,0)),IFERROR(INDEX(CRM!X:X,MATCH(G2,CRM!G:G,0)),"")))

Step 3: what to do with each bucket?#

Filter column I, then handle each bucket separately.

• "already": do not import these rows again. If the received file brings information you lack (a website, an email), update the CRM record using the ID you pulled in. See company data enrichment.
• "to check": open both records side by side and decide by hand. They are often chain or switchboard numbers. In our test, there were 64 out of 1,513 received rows, about 4 in 100.
• "new": these are the rows to import. Out of 100 rows marked "new" in our test, 96 really were. Before calling, qualify the database.

Make a copy of the workbook and of your CRM before any import. Run each new file through these formulas before importing it: it is the best way to avoid duplicates in the future. Next step: build your prospect list.

Why these formulas: what each column delivered#

We compared 1,513 received rows with two test CRMs, auto repair shops and restaurants, knowing the right answer for each row (details in the method).

• The phone number alone finds 91 out of 100 rows the CRM already had. Its trap is the shared number: switchboard, network, vending machines of the same brand. Of the 18 rows wrongly flagged by the phone number, 14 came from numbers carried by several rows. Hence the COUNTIF(E:E,E2)=1 test in the formula.
• The name with the postal code finds only 29 out of 100 rows, because names are rarely written the same way. But it is rarely wrong: 2 errors out of 165 rows flagged, two restaurants of the same chain in the same postal code.
• The email address, when identical, measured on restaurants only, flagged no row by mistake: the 64 rows it found were all already in the CRM. But added to the phone number and the name, it found only 5 more rows: the status formula does without it.

Here is how the status formula sorted the 1,513 received rows:

In practice, 99 out of 100 "already" rows are right. Without the "to check" bucket, the phone number and the name would have flagged 19 rows as already present by mistake; with it, 6 remain. Conversely, out of 100 rows the CRM already had, 6 slip through as new.

We also reran the test with the prompt from the guide with Claude, on the same files and with the same known answer. It lets through about a third as many already-known rows (12 versus 35) and leaves less than half as many to check (27 versus 64). The formulas, for their part, need nothing but a spreadsheet.

What about your CRM's duplicate tool?#

These tools look for duplicates inside the CRM, once rows are imported: so prepare your columns beforehand. Here is what their documentation says.

A domain or phone number shared by a chain can merge two different locations. In our test, on restaurants, the website made 9 errors out of 86 rows flagged. Keep a copy before any merge.

Frequently asked questions#

What about duplicates inside my own file?#

Apply the formulas from step 1. Then color the duplicate values in column H with conditional formatting and check them: a chain number can link two locations. Make a copy, sort column H to group the filled rows at the top, select only those and, after checking, apply Data > Remove duplicates; for rows without a phone number, do the same with column G. To get the list of keys without duplicates, without deleting anything, =UNIQUE(H2:H1000) does it in Excel 2021, 2024 and Microsoft 365. In Google Sheets, the path is Data > Data cleanup > Remove duplicates, which treats values that differ only in capitalization as duplicates.

Do I need a SIRET number in my file?#

No. It helps when it is on both sides. The SIRET number is the 14-digit ID of a French business location; its first 9 digits, the SIREN number (the French company ID), identify the company. Two rows with the same SIREN can therefore be two different locations. See finding a company's SIRET number and the Sirene database.

Method#

We tested the formulas on two cases, auto repair shops and restaurants, with the right answer known for each row. Each received row was sorted by the formulas, then compared with that answer; some of the cases were reviewed a second time by Scalon.

Sources#

• Rechercher et supprimer des doublons, aide Microsoft
• Fonction RECHERCHEX, aide Microsoft
• Fonction UNIQUE, aide Microsoft
• Fonction NB.SI, aide Microsoft
• Supprimer les doublons, aide Google Sheets
• Liste des fonctions Google Sheets
• Review and manage duplicate records, HubSpot (updated October 23, 2026)
• Deduplicate records in HubSpot (updated October 2, 2026)
• Standard Account Matching Rule, Salesforce (updated September 25, 2026)
• How does the Merge Duplicates feature identify duplicates, Pipedrive (updated September 3, 2026)
• Merge Duplicates, Pipedrive (updated September 3, 2026)
• Siret, définition, Insee
• Siren, définition, Insee
• Deduplicating a company database with Claude, Scalon

Cite this study#