# How to deduplicate a customer file in Excel or Google Sheets, without AI > Use copy-and-paste Excel or Sheets formulas to find the rows your CRM already has. Method tested on 1,513 businesses: 94 out of 100 found. Source: https://scalon.fr/en/blog/deduplicate-customer-file-excel --- [← The blog](/en/blog) Guide · Data Spot the companies your CRM already has in a file you received, with copy-and-paste formulas: the simple method, tested on 1,513 French businesses, and what it leaves you to check. By [Nicolas Morillot](https://www.linkedin.com/in/nicolas-morillot-22b656174/) · Co-founder of Scalon The short answer Excel's Remove Duplicates does not compare two files written differently. Put phone numbers, names and postal codes in the same format, then look up each received row in your CRM with COUNTIF. Tested by Scalon in November 2026 on 1,513 French businesses, this method finds 94 out of 100 rows the CRM already had. In a hurry? Go straight to the formulas to copy. Simpler: with Claude If you use Claude, there is a simpler way: you copy one prompt, Claude compares the two files and sorts each row, with no formulas to write. Our guide: [how to deduplicate a business database with Claude](/en/blog/deduplicate-database). Get the list of businesses in your market, with the contact details available: tell us which one. Published on November 5, 2026 Key figures 0 rows flagged by Remove Duplicates Out of 1,513 received rows compared with two test CRMs, columns as they were: the phone numbers were not written the same way. 94 out of 100 rows already in the CRM found by our formulas Phone number normalized to 10 digits, then name and postal code; 9 of these 94 are marked \"to check\" and reviewed by hand. 99 out of 100 rows marked \"already\" really were 477 out of 483 in our test: you can leave them out of your import without reviewing them. ## In this article - Why doesn't Remove Duplicates find anything between two files? - Are the formulas enough for your CRM? - Step 1: export both files and prepare the columns - Step 2: mark the rows already present - Step 3: what to do with each bucket? - Why these formulas: what each column delivered - What about your CRM's duplicate tool? - Frequently asked questions - Method - Sources - Cite this study ## 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: | File | Name · Phone | | --- | --- | | CRM | SARL Garage Martin · 02 99 00 00 01 | | Received | Garage Martin · +33 2 99 00 00 01 | 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: Share of received rows already in two test CRMs that each method finds (Scalon, November 2026). 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](/en/blog/deduplicate-database). ## 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: | Your CRM | Rows found out of 100 | Advice | | --- | --- | --- | | Auto repair shops, phone number on every row | 95 | The formulas are enough | | Restaurants, phone number on 93% of rows | 92 | The formulas are enough | | No phone number | 15 to 51 depending on our two cases (name + postal code only) | Switch to the [guide with Claude](/en/blog/deduplicate-database) | 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: | Received row (A · B · C) | E: phone | F: name | G: name + postal code key | | --- | --- | --- | --- | | SARL Garage Martin · 35000 · +33 2 99 00 00 01 | 0299000001 | GARAGE MARTIN | GARAGE MARTIN\|35000 | | Boulangerie du Parc · 1000 · 04 50 00 00 04 | 0450000004 | BOULANGERIE DU PARC | BOULANGERIE DU PARC\|01000 | | Pizza Express · 53000 · 33243000003 | 0243000003 | PIZZA EXPRESS | PIZZA EXPRESS\|53000 | Google Sheets uses the same English function names ([list of Google Sheets functions](https://support.google.com/docs/table/25273?hl=fr)). 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. **Prospecting local businesses?** Get the list for your market, your area and your target, ready to call. ## 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](https://support.microsoft.com/fr-fr/office/rechercher-et-supprimer-des-doublons-00e35bea-b46a-4d5d-b28e-66a552dc138d) 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](/en/blog/b2b-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](/en/blog/qualify-b2b-prospect-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](/en/blog/b2b-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: | Bucket | Rows | Of which already in the CRM | | --- | --- | --- | | "already" | 483 | 477 | | "to check" | 64 | 51 | | "new" | 966 | 35 | 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. Need the list for your market? We send it with the contact details available, for the scope you choose. ## 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. | Tool | What it compares | Good to know | | --- | --- | --- | | HubSpot | Companies: domain, name, country, phone number, industry | Duplicates tool on Professional or Enterprise plans; on import, a company with a known domain updates the existing record; merges are permanent | | Salesforce | Accounts: name with address, city, postal code or phone number; or website with phone number or address | Rule designed for North American numbers, per its documentation: check your number format | | Pipedrive | Organizations: same name and same address | Merges are permanent | 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](/en/blog/find-siret-number) and [the Sirene database](/en/blog/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. Method details and limits **The test cases.** Auto repair shops: 448 records from a directory of professionals in one département (a French administrative area) serve as the CRM, compared with 963 businesses from Scalon's data. Restaurants: 397 records from a restaurant directory in another département, compared with 550 Scalon businesses. The test CRM records are taken as published, without cleaning. **The known answer.** Two rows refer to the same business if they are matched in Scalon's data. Disagreements between a simple key and these matches were reviewed blind in a second Scalon review, without seeing the answers: 99 pairs for this article, 98 "not the same" and 1 "same" (same address, same phone number, changed name), added to the known answer. Our matches can be wrong: a rule is adopted only if its measured error stays under 5%. **563 rows already present here, 557 in the guide with Claude.** The guide checked a row only through the matches proposed by its own run. Here, each row is checked against all our matches and all reviews, plus the pair above: six more rows are therefore recognized as already present. The Claude prompt is scored against this same answer, to compare on equal terms: 531 right out of 536 "already in my data," 20 of the 27 "to check" already present, 12 already present marked new. **The formulas.** The figures come from a replica of the logic of the article's formulas, computed on all received rows. COUNTIF also treats `*` and `?` as wildcards and compares phone numbers as numeric values: no effect in our test (no name affected, 4 phone numbers out of format). The name as is with the postal code, ignoring capitalization as COUNTIF and the button do, found 28 out of 100 rows, versus 29 with the formulas; the phone number as written, none. The "0" of the Remove Duplicates button holds for the three columns name, postal code and phone number compared together: on name and postal code alone, it would have removed 162 received rows. The formulas were tried in Google Sheets on made-up rows (a +33 phone number, a postal code without its 0, a number carried by two rows, an empty row), with the English names of the same functions, not in Microsoft Excel. **Other sources.** The tutorials on the results page explain the button and conditional formatting on a single file, like Microsoft's help; we found none that measures what its method finds between two company files. **The limits.** The SIRET number is not measured: our test CRMs did not have it. The formulas remove neither accents, nor punctuation, nor rarer legal forms (SCI, ETS); doing so would have raised name + postal code from 29 to 35 out of 100. Power Query and fuzzy matching were not tested. Two sectors and two départements only: another trade, another area or a CRM typed by hand may give other results. ## Sources - [Rechercher et supprimer des doublons, aide Microsoft](https://support.microsoft.com/fr-fr/excel/find-and-remove-duplicates) - [Fonction RECHERCHEX, aide Microsoft](https://support.microsoft.com/fr-fr/office/fonction-recherchex-b7fd680e-6d10-43e6-84f9-88eae8bf5929) - [Fonction UNIQUE, aide Microsoft](https://support.microsoft.com/fr-fr/office/fonction-unique-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e) - [Fonction NB.SI, aide Microsoft](https://support.microsoft.com/fr-fr/office/nb-si-nb-si-fonction-e0de10c6-f885-4e71-abb4-1f464816df34) - [Supprimer les doublons, aide Google Sheets](https://support.google.com/docs/answer/6325535?hl=fr) - [Liste des fonctions Google Sheets](https://support.google.com/docs/table/25273?hl=fr) - [Review and manage duplicate records, HubSpot (updated October 23, 2026)](https://knowledge.hubspot.com/records/manage-duplicate-records) - [Deduplicate records in HubSpot (updated October 2, 2026)](https://knowledge.hubspot.com/records/deduplication-of-records) - [Standard Account Matching Rule, Salesforce (updated September 25, 2026)](https://help.salesforce.com/s/articleView?language=en_US&id=sales.matching_rules_standard_account_rule.htm&type=5) - [How does the Merge Duplicates feature identify duplicates, Pipedrive (updated September 3, 2026)](https://support.pipedrive.com/en/article/how-does-the-merge-duplicates-feature-identify-duplicates-in-pipedrive) - [Merge Duplicates, Pipedrive (updated September 3, 2026)](https://support.pipedrive.com/en/article/merge-duplicates) - [Siret, définition, Insee](https://www.insee.fr/fr/metadonnees/definition/c1841) - [Siren, définition, Insee](https://www.insee.fr/fr/metadonnees/definition/c2047) - [Deduplicating a company database with Claude, Scalon](/en/blog/deduplicate-database) ## Cite this study The full citation, with sample sizes > Scalon Observatory, "How to deduplicate a customer file in Excel or Google Sheets, without AI," November 5, 2026: out of 1,513 received rows compared with two test CRMs of 448 and 397 rows, the formulas find 94 out of 100 rows already present; 477 of the 483 rows marked "already" are right; Remove Duplicates, columns as they are, flags none. [https://scalon.fr/en/blog/deduplicate-customer-file-excel](/en/blog/deduplicate-customer-file-excel) ## Do you sell to local businesses and SMBs? Restaurants, professional practices, agencies, auto repair shops: Scalon links each business location to its company and its public contact details. [For professionals: get 100 businesses in my area](/en#call) More on this topic - [B2B sales prospecting plan: how to build one](/en/blog/sales-prospecting-plan) : The steps to build your plan: goals, target, qualified list and channels. - [TAM SAM SOM Method: Calculate Your Addressable Market](/en/blog/tam-sam-som) : Define each market level and work it out on a real example. - [B2B sales prospecting tools: what they're for](/en/blog/b2b-prospecting-tools) : What each family of tools does, and what none of them replaces. [All articles →](/en/blog)