La fonction « Supprimer les doublons » d’Excel ne compare que des chaînes strictement identiques. Un espace insécable en fin de cellule, une majuscule mal placée ou un accent oublié suffisent à laisser passer un doublon. Enlever des doublons sur Excel de façon fiable suppose de normaliser les données avant toute suppression, puis de choisir le bon outil selon la récurrence du nettoyage.
Normaliser les cellules avant de supprimer les doublons sur Excel
La majorité des doublons résiduels après un clic sur « Supprimer les doublons » sont des quasi-doublons : différences de casse, espaces surnuméraires, accents manquants ou formats de date hétérogènes. Excel les traite comme des valeurs distinctes.
A voir aussi : Calculer rapidement 20% d'une somme : astuce Excel expliquée
Nous recommandons de systématiser une colonne de travail temporaire où chaque valeur est nettoyée par une formule combinée. SUPPRESPACE(MINUSCULE(SUBSTITUE(A2;CARSPE(160); » « ))) enchaîne trois opérations : conversion en minuscules, remplacement de l’espace insécable (caractère 160) par un espace standard, puis suppression des espaces en trop.
Pour les accents, la fonction SUBSTITUE imbriquée reste la seule option native. Remplacer « é », « è », « ê » par « e » dans la colonne temporaire permet une comparaison insensible aux diacritiques. C’est fastidieux, mais c’est la seule approche sans macro ni complément.
A voir aussi : Analyse rapide sur Excel : méthodes et astuces pour excel
Une fois la colonne normalisée créée, appliquez la suppression des doublons sur cette colonne plutôt que sur les données brutes. Supprimez ensuite la colonne de travail. Cette étape préparatoire ajoute deux minutes au processus, mais elle élimine la quasi-totalité des faux négatifs que le bouton natif laisse passer.
Formule NB.SI et mise en forme conditionnelle : repérer avant de supprimer
Supprimer à l’aveugle pose un problème concret : Excel conserve la première occurrence et supprime les suivantes, sans possibilité de choisir laquelle garder. Sur un fichier de contacts où la ligne la plus récente porte les coordonnées à jour, c’est l’ancienne version qui reste.
Identifier les doublons avec NB.SI
La formule =NB.SI(A:A;A2)>1 renvoie VRAI pour chaque cellule dont la valeur apparaît plus d’une fois dans la colonne. Appliquée dans une colonne auxiliaire, elle permet de filtrer les lignes concernées et de décider manuellement laquelle conserver.
Pour un contrôle sur plusieurs colonnes (nom + prénom + e-mail, par exemple), concaténez les critères dans la formule ou dans une colonne dédiée. La logique reste la même : construire une clé composite avant de compter les occurrences.
Mise en forme conditionnelle pour le contrôle visuel
La mise en forme conditionnelle avec la règle « Valeurs en double » (onglet Accueil > Styles) colore instantanément les cellules concernées. Nous l’utilisons comme filet de sécurité après un nettoyage automatisé : si des cellules restent colorées, c’est que la normalisation a raté un cas.
- Sélectionnez la plage de données sans les en-têtes pour éviter les faux positifs sur les titres de colonnes.
- Appliquez la règle sur la colonne normalisée (pas sur la colonne brute) pour capter aussi les quasi-doublons.
- Combinez avec un filtre par couleur pour isoler uniquement les lignes en double et les traiter par lot.
Power Query pour enlever des doublons sur des fichiers récurrents
Power Query transforme un nettoyage ponctuel en processus reproductible. Sur des fichiers alimentés régulièrement (exports CRM, logs, bases produits), relancer manuellement la suppression des doublons à chaque mise à jour est une perte de temps et une source d’erreurs.
Dans l’éditeur Power Query (onglet Données > Obtenir et transformer), la commande « Supprimer les lignes en double » s’applique sur une ou plusieurs colonnes après les étapes de transformation. La différence avec le bouton natif : chaque étape de nettoyage (suppression d’espaces, changement de casse, déduplication) est enregistrée dans une requête. À la prochaine actualisation du fichier source, un simple clic sur « Actualiser » relance toute la chaîne.
Power Query gère aussi nativement la casse et les espaces dans ses transformations de texte, ce qui rend inutile la colonne temporaire décrite plus haut. Pour les fichiers volumineux (plusieurs dizaines de milliers de lignes), la performance est nettement meilleure qu’une formule NB.SI appliquée à toute une colonne.
Erreurs fréquentes qui font échouer la suppression de doublons
Certains réflexes courants sabotent le résultat sans que l’utilisateur s’en rende compte.
- Oublier de décocher les colonnes non pertinentes dans la boîte de dialogue « Supprimer les doublons ». Si toutes les colonnes sont cochées, deux lignes avec le même nom mais un numéro de téléphone différent seront considérées comme distinctes.
- Lancer la suppression sur des cellules fusionnées. Excel refuse l’opération ou produit des résultats incohérents. Défusionnez d’abord.
- Ne pas trier les données avant la suppression quand l’ordre de conservation compte. Excel garde la première occurrence dans l’ordre actuel du tableau, pas la plus récente.
- Travailler sur la plage entière sans exclure les lignes d’en-tête. Le bouton natif propose de traiter la première ligne comme en-tête, mais un mauvais clic suffit à supprimer un intitulé de colonne considéré comme doublon.
Le piège des formats de date et de nombre
Deux cellules peuvent afficher « 01/06/2025 » et pourtant contenir des valeurs différentes : l’une stocke un nombre sérialisé, l’autre une chaîne de texte. La fonction ESTNUM() appliquée aux deux cellules révèle l’incohérence. Uniformisez le format avec DATEVAL() ou CNUM() avant toute comparaison.
Le même problème se pose avec les nombres stockés comme texte après un import CSV. La petite flèche verte dans le coin supérieur gauche de la cellule signale ce décalage. Convertissez en lot via « Convertir en nombre » ou par un collage spécial avec multiplication par 1.
Un fichier correctement normalisé avant la déduplication produit des résultats fiables dès le premier passage. Le temps investi dans la préparation des données se récupère en évitant les allers-retours de vérification, surtout sur les fichiers partagés où les erreurs de saisie s’accumulent entre chaque intervenant.
