On récupère un fichier client exporté d’un CRM, on le colle dans Excel, et dès le premier tri alphabétique, le constat tombe : des dizaines de lignes en double, des noms écrits différemment, des adresses mail avec ou sans majuscule. La fonction « Supprimer les doublons » d’Excel ne suffit pas à traiter ce type de bazar. Pour nettoyer une base client en profondeur, il faut préparer les données avant même de chercher les doublons.
Préparer les données avant de supprimer les doublons sur Excel
On ouvre le fichier, on lance directement « Supprimer les doublons », et Excel nous annonce fièrement avoir retiré trois lignes. Le problème, c’est que la base en contenait bien plus, masqués par des différences invisibles : un espace en fin de cellule, une majuscule en trop, un format de téléphone avec ou sans indicatif.
A lire en complément : Utiliser Excel comme base de données : bonnes pratiques et conseils
Avant toute suppression, on passe par un nettoyage structurel. La fonction SUPPRESPACE retire les espaces superflus en début, milieu et fin de cellule. La fonction MINUSCULE (ou MAJUSCULE) uniformise la casse. Pour les numéros de téléphone, on force un format texte homogène avec SUBSTITUE afin de retirer les points, tirets ou espaces parasites.
Ce travail préparatoire transforme « Jean Dupont » et « jean dupont » en une même chaîne. Sans cette étape, Excel considère ces deux entrées comme distinctes et ne les signale jamais comme doublons.
A lire également : Créer un tableau facilement sur Excel et Word avec ces astuces
Un point souvent négligé concerne les caractères spéciaux invisibles importés depuis certains CRM. Les retours chariot cachés dans une cellule, les apostrophes typographiques différentes de l’apostrophe droite ou les espaces insécables faussent la comparaison. La fonction EPURAGE supprime les caractères non imprimables, et un SUBSTITUE ciblé sur le code caractère 160 (espace insécable) complète le nettoyage.
Mise en forme conditionnelle et NB.SI : repérer les doublons sans les supprimer
Supprimer à l’aveugle, c’est risquer de perdre la ligne qui contient le plus d’informations. On commence par repérer les doublons visuellement avant de décider quoi garder.
Mise en forme conditionnelle pour un repérage visuel
On sélectionne la colonne clé (adresse mail, par exemple), puis Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance > Valeurs en double. Les cellules concernées passent en rouge. On voit immédiatement l’ampleur du problème.
NB.SI pour compter les occurrences
On ajoute une colonne auxiliaire avec la formule =NB.SI($B:$B;B2) qui renvoie le nombre de fois où chaque valeur apparaît. Toute cellule affichant un résultat supérieur à 1 signale un doublon. Cette méthode permet de trier ensuite par nombre d’occurrences et de traiter les cas un par un, en gardant la ligne la plus complète.
Les retours varient sur ce point : certains préfèrent trier sur la colonne NB.SI, d’autres filtrent directement les valeurs supérieures à 1. Les deux approches fonctionnent, l’objectif reste de ne jamais supprimer sans avoir vu ce qu’on supprime.
Combiner NB.SI.ENS pour les doublons partiels
Quand le doublon ne porte pas sur une seule colonne mais sur une combinaison (même nom ET même ville), NB.SI ne suffit plus. La formule =NB.SI.ENS($B:$B;B2;$F:$F;F2) vérifie plusieurs critères simultanément. On obtient un comptage plus fin qui distingue les vrais doublons des homonymes situés dans des villes différentes.
Power Query pour un nettoyage reproductible de la base client
Quand on gère une base de plusieurs milliers de lignes, les formules manuelles montrent leurs limites. Power Query, intégré dans Excel depuis la version 2016, change la donne.
On importe le fichier via Données > Obtenir des données. Power Query ouvre un éditeur où chaque transformation (suppression de colonnes inutiles, remplacement de valeurs, division d’une colonne nom/prénom, uniformisation de la casse) s’enregistre comme une étape. Toute la chaîne de nettoyage se rejoue automatiquement à chaque actualisation des données.
Pour supprimer les doublons dans Power Query, on sélectionne les colonnes de référence (mail, téléphone, ou combinaison nom + code postal), puis clic droit > Supprimer les doublons. Power Query conserve la première occurrence dans l’ordre du tableau source.
- Standardiser les formats (dates, téléphones, codes postaux) via Remplacer les valeurs ou Fractionner la colonne
- Fusionner prénom et nom dans un champ unique pour fiabiliser la détection
- Filtrer les lignes vides ou incomplètes avant le dédoublonnage
- Réappliquer le tout en un clic lors du prochain export CRM
L’avantage concret : on ne refait pas le travail à chaque import mensuel. La requête enregistrée s’applique au nouveau jeu de données sans manipulation supplémentaire.
Validation des données Excel : bloquer les doublons à la saisie
Nettoyer, c’est bien. Empêcher les doublons d’entrer dans la base, c’est mieux. Excel permet de poser un verrou directement sur une colonne grâce à la validation des données.
On sélectionne la colonne cible (par exemple la colonne mail), puis Données > Validation des données. Dans l’onglet Options, on choisit « Personnalisé » et on entre la formule =NB.SI($B:$B;B2)<=1. Dans l'onglet Alerte d'erreur, on rédige un message du type "Cette adresse existe déjà dans la base".
À partir de là, toute saisie d’une valeur déjà présente déclenche un message d’erreur. La base reste propre sans intervention humaine après coup. On peut combiner cette validation avec NB.SI.ENS pour vérifier plusieurs critères simultanément (même nom ET même code postal, par exemple).
Cas concret : dédoublonner une base client exportée d’un CRM
On reçoit un export de plusieurs milliers de contacts. Les colonnes sont : nom, prénom, société, mail, téléphone, ville, code postal. Le fichier provient de deux sources fusionnées, d’où un volume élevé de doublons partiels.
La séquence de nettoyage qui fonctionne :
- Importer dans Power Query et supprimer les colonnes sans valeur (notes internes, champs vides à plus de la moitié)
- Appliquer SUPPRESPACE et MINUSCULE sur les colonnes nom, prénom et mail via « Transformer »
- Créer une colonne calculée concaténant mail + code postal comme clé de dédoublonnage
- Supprimer les doublons sur cette clé composite
- Charger le résultat dans une nouvelle feuille Excel propre
Cette approche évite le piège classique : dédoublonner sur le seul nom de famille, ce qui supprime des homonymes légitimes. La combinaison de deux ou trois colonnes comme clé de déduplication réduit ce risque de façon significative.
Un fichier client nettoyé une fois par trimestre avec une requête Power Query enregistrée prend quelques minutes. Le même travail fait manuellement avec des tris et des suppressions ligne par ligne peut occuper une demi-journée. La différence ne se joue pas sur la technique, mais sur le fait d’avoir posé la bonne structure de nettoyage dès le départ.
