Vous ouvrez un fichier client, vous lancez une recherche de doublons dans Excel, et le résultat affiche des centaines de lignes surlignées. Le réflexe est de tout supprimer. Le problème : une partie de ces « doublons » n’en sont pas.
Des espaces invisibles en fin de cellule, un format texte sur une colonne et un format nombre sur une autre, une majuscule en trop – ces micro-écarts suffisent à fausser toute la détection. Comprendre pourquoi Excel se trompe avant de cliquer sur « Supprimer » évite de perdre des données utiles.
A lire en complément : Astuces simples pour éliminer efficacement les doublons
Espaces invisibles et faux doublons dans Excel : le piège le plus courant
Avant même de parler de mise en forme conditionnelle ou de formules, un point mérite toute votre attention. Un doublon apparent n’est pas toujours un doublon réel. Excel compare les cellules caractère par caractère. Un simple espace en fin de chaîne rend deux valeurs différentes à ses yeux.
Prenez deux cellules contenant « Dupont » et « Dupont » (avec un espace final). Visuellement, elles sont identiques. Pour Excel, ce sont deux valeurs distinctes. La mise en forme conditionnelle ne les surlignera pas comme doublons. L’outil « Supprimer les doublons » les ignorera aussi.
A voir aussi : Figer plusieurs lignes sur Excel : astuces pour éviter les erreurs les plus fréquentes
Comment repérer ces espaces fantômes
La fonction NBCAR (ou LEN en anglais) révèle la longueur réelle d’une cellule. Si « Dupont » affiche 6 caractères dans une cellule et 7 dans une autre, vous tenez votre espace invisible. La fonction SUPPRESPACE (TRIM) nettoie ces résidus en une seule opération sur toute la colonne.
Ce nettoyage devrait être votre première étape, systématiquement, avant toute recherche de doublons. Appliquer SUPPRESPACE sur vos colonnes de texte prend quelques secondes et évite des heures de tri manuel inutile.
Format texte contre format nombre : quand Excel voit deux valeurs différentes dans un même chiffre
Vous avez déjà remarqué un petit triangle vert dans le coin d’une cellule ? C’est l’indicateur d’Excel pour signaler qu’un nombre est stocké comme texte. Ce détail change tout pour la détection de doublons.
Une cellule contenant le nombre 1234 et une autre contenant le texte « 1234 » ne correspondent pas lors d’une comparaison. Excel traite le format de stockage, pas l’apparence visuelle. Les exports depuis un ERP, un CRM ou un fichier CSV produisent souvent ce type de mélange.
Corriger le format avant de chercher
Sélectionnez la colonne concernée. Utilisez la fonction CNUM (VALUE) pour forcer la conversion en nombre. Autre méthode rapide : copiez une cellule vide, sélectionnez la plage problématique, puis faites « Collage spécial » > « Addition ». Cette astuce force Excel à recalculer les cellules et aux reconvertir en valeurs numériques.
- Vérifiez l’alignement : par défaut, les nombres s’alignent à droite et le texte à gauche. Un « nombre » aligné à gauche est probablement stocké comme texte.
- Utilisez la fonction TYPE pour identifier le format réel d’une cellule (1 pour nombre, 2 pour texte).
- Sur les fichiers importés, appliquez systématiquement une conversion de format avant toute analyse.
Cellules fusionnées et colonnes mal structurées : des obstacles à la détection de doublons Excel
Les cellules fusionnées posent un problème que beaucoup sous-estiment. Excel ne peut pas trier ni filtrer correctement une plage contenant des fusions. La recherche de doublons sur une colonne où certaines cellules sont fusionnées produit des résultats incohérents, voire des messages d’erreur.
La raison est simple. Une cellule fusionnée sur trois lignes ne contient qu’une seule valeur, mais occupe l’espace de trois. Les deux lignes « vides » en dessous ne sont pas vides au sens classique : elles font partie de la fusion. Excel ne sait pas les comparer individuellement.
La bonne pratique pour éviter ce piège
Supprimez toutes les fusions avant de lancer une recherche de doublons. Sélectionnez le tableau entier, puis cliquez sur « Fusionner et centrer » pour annuler les fusions existantes. Les cellules récupèrent alors une structure individuelle. Remplissez ensuite les cellules vides laissées par la défusion (un simple « Ctrl+G » > « Cellules vides » > saisie de la référence supérieure avec Ctrl+Entrée fait le travail).
Si vous avez besoin d’un rendu visuel centré sur plusieurs colonnes, utilisez l’option « Centrer sur plusieurs colonnes » dans le format de cellule, sous l’onglet Alignement. Cette option préserve la structure sans bloquer les fonctions de tri et de filtrage.
NB.SI et mise en forme conditionnelle : bien paramétrer la formule pour des résultats fiables
La plupart des guides recommandent la mise en forme conditionnelle avec la règle « Valeurs en double ». Cette méthode fonctionne, mais elle a une limite : elle compare les cellules telles quelles, sans tenir compte des problèmes de format ou d’espaces décrits plus haut.
Pour une détection plus précise, la fonction NB.SI (COUNTIF) offre un meilleur contrôle. Appliquée dans une colonne auxiliaire, elle compte le nombre d’occurrences de chaque valeur dans la plage.
Exemple concret de formule
En supposant que vos données sont en colonne A, à partir de la ligne 2 : =NB.SI($A:$A;A2). Toute valeur supérieure à 1 signale un doublon. Vous pouvez ensuite filtrer cette colonne auxiliaire pour n’afficher que les lignes concernées.
- Combinez SUPPRESPACE et MINUSCULE dans la formule pour neutraliser les écarts de casse et d’espaces : =NB.SI.ENS($A:$A;SUPPRESPACE(MINUSCULE(A2)))
- Pour détecter les doublons sur plusieurs colonnes simultanément, concaténez les valeurs : =NB.SI($D:$D;A2&B2&C2), en plaçant la concaténation correspondante en colonne D
- Gardez toujours la colonne auxiliaire visible tant que le nettoyage n’est pas terminé, pour pouvoir vérifier chaque cas
Quand la recherche de doublons révèle un problème de gouvernance du fichier
Un fichier Excel partagé entre plusieurs personnes accumule naturellement les doublons. Chaque contributeur ajoute des lignes sans vérifier si l’entrée existe déjà. Les copier-coller entre onglets ou entre fichiers multiplient les risques.
Les doublons récurrents signalent souvent que le fichier a dépassé son usage initial. Un tableau de suivi conçu pour une personne ne supporte pas la saisie simultanée de cinq collaborateurs sans règles de contrôle.
Quelques mesures réduisent le problème à la source. La validation de données (onglet Données > Validation) permet d’interdire la saisie d’une valeur déjà présente dans la colonne. Configurer cette validation sur les colonnes clés empêche la création de doublons avant qu’ils n’apparaissent.
Pour les fichiers à usage récurrent avec un volume qui grossit, la question se pose de migrer vers un outil avec gestion native des doublons. Les bases de données ou les CRM intègrent des contrôles d’unicité que Excel ne propose pas nativement. Le tableur reste adapté à l’analyse ponctuelle, moins à la gestion collaborative de données vivantes.
