Trouver les doublons dans un tableau Excel ne se limite pas à colorier des cellules identiques. Selon la taille du fichier et le type d’analyse, la méthode choisie change radicalement le résultat obtenu. Mise en forme conditionnelle, formules NB.SI, fonctions dynamiques comme UNIQUE ou FILTRE : chaque approche répond à un besoin différent, et certaines présentent des limites que les tutoriels classiques mentionnent rarement.
Comparatif des méthodes pour détecter les doublons Excel
Avant de choisir une technique, il faut comprendre ce que chacune fait réellement, et surtout ce qu’elle ne fait pas. Le tableau ci-dessous synthétise les trois grandes approches disponibles dans Excel pour Microsoft 365.
A découvrir également : Excel nouvelle génération : comparatif des logiciels de tableur
| Méthode | Type de détection | Mise à jour automatique | Gère plusieurs colonnes | Suppression possible |
|---|---|---|---|---|
| Mise en forme conditionnelle | Visuelle (surlignage) | Oui | Non (colonne par colonne) | Non |
| NB.SI / NB.SI.ENS | Formule (comptage) | Oui | Oui (avec NB.SI.ENS) | Non (tri + suppression manuelle) |
| Fonctions dynamiques (UNIQUE, FILTRE) | Extraction dans une nouvelle plage | Oui | Oui | Non (vue séparée sans doublons) |
La mise en forme conditionnelle reste la plus rapide à mettre en place. Deux clics suffisent : sélectionner la plage de cellules, puis Accueil > Mise en forme conditionnelle > Valeurs en double. Les cellules dupliquées apparaissent en couleur.
En revanche, cette méthode compare les valeurs cellule par cellule. Si un doublon repose sur la combinaison de plusieurs colonnes (même nom ET même date, par exemple), la mise en forme conditionnelle ne détecte pas ce type de doublon.
A lire en complément : Instructions if contains Excel : Comment procéder facilement ?
NB.SI et NB.SI.ENS : contrôler les doublons sur plusieurs colonnes
La fonction NB.SI compte le nombre d’occurrences d’une valeur dans une plage. La formule =NB.SI(A:A;A2) renvoie le nombre de fois où la valeur de A2 apparaît dans la colonne A. Toute cellule renvoyant un résultat supérieur à 1 signale un doublon.
Pour des données plus complexes, NB.SI.ENS permet d’ajouter plusieurs critères. La formule =NB.SI.ENS(A:A;A2;B:B;B2) ne compte que les lignes où la colonne A ET la colonne B correspondent simultanément. C’est la seule manière fiable de détecter des doublons de lignes entières sans recourir à Power Query.
Piège fréquent avec NB.SI sur de grandes plages
Appliquer NB.SI sur une colonne entière (A:A) ralentit considérablement le calcul sur les fichiers volumineux. Limiter la plage à la zone réellement utilisée (A2:A5000 par exemple) améliore les performances de façon notable.
Un autre piège concerne les espaces invisibles. Deux cellules qui semblent identiques peuvent renvoyer des résultats différents si l’une contient un espace en fin de chaîne. La fonction SUPPRESPACE combinée à NB.SI résout ce problème :
=NB.SI(A$2:A$5000;SUPPRESPACE(A2))nettoie les espaces avant la comparaison- NB.SI.ENS accepte aussi SUPPRESPACE sur chaque critère, mais la formule devient vite longue
- Pour un nettoyage systématique, une colonne auxiliaire avec SUPPRESPACE reste plus lisible qu’une formule imbriquée
Fonctions dynamiques UNIQUE et FILTRE : une approche sans suppression
Les fonctions dynamiques introduites dans Excel pour Microsoft 365 changent la logique de travail. Au lieu de marquer ou compter les doublons dans la plage d’origine, elles extraient les données dans une nouvelle zone qui se met à jour automatiquement quand les données source changent.
La fonction UNIQUE renvoie la liste des valeurs distinctes d’une plage. La formule =UNIQUE(A2:A500) produit une colonne filtrée sans aucune répétition. Combinée avec FILTRE, elle permet d’isoler uniquement les lignes qui apparaissent plus d’une fois.
UNIQUE sur plusieurs colonnes
Appliquée à une plage multi-colonnes (=UNIQUE(A2:C500)), la fonction compare les lignes entières. Deux lignes ne sont considérées comme doublons que si toutes les cellules de la plage correspondent. Ce comportement est plus intuitif que NB.SI.ENS pour les utilisateurs qui travaillent sur des tableaux structurés.
La limite principale : UNIQUE et FILTRE ne sont disponibles que dans Excel pour Microsoft 365 et Excel 2021. Les versions antérieures (Excel 2016, 2019) n’y ont pas accès.
Supprimer les doublons Excel : ce que l’outil natif fait (et ne fait pas)
L’onglet Données > Supprimer les doublons propose un outil dédié. Il affiche la liste des colonnes et permet de cocher celles sur lesquelles baser la comparaison. Après validation, Excel supprime définitivement les lignes dupliquées et ne conserve que la première occurrence.
Ce comportement pose un problème si l’ordre des lignes n’est pas contrôlé. Excel conserve la première ligne rencontrée dans l’ordre du tableau. Si les données ne sont pas triées, la ligne conservée peut ne pas être la plus récente ou la plus complète.
- Trier le tableau avant suppression garantit que la ligne conservée est celle souhaitée (par date décroissante, par exemple)
- Travailler sur une copie du fichier protège les données d’origine, car la suppression est irréversible après enregistrement
- Combiner Supprimer les doublons avec un filtre préalable permet de ne traiter qu’un sous-ensemble du tableau
Power Query et Power Automate : automatiser le contrôle des doublons
Pour les fichiers alimentés régulièrement (exports CRM, fichiers de stock, listes de contacts), la détection ponctuelle ne suffit pas. Power Query permet de créer une requête de dédoublonnage réutilisable qui s’exécute à chaque actualisation des données.
Dans Power Query, l’option « Supprimer les lignes en double » fonctionne sur une ou plusieurs colonnes sélectionnées. La requête conserve sa logique même si le volume de données change d’un import à l’autre.
Microsoft a également développé l’intégration entre Excel et Power Automate, qui permet de déclencher automatiquement des flux de contrôle (notifications, validations) lorsqu’un fichier contenant des doublons est déposé dans OneDrive ou SharePoint. Cette logique de pipeline de données dépasse la simple vérification manuelle.
Le choix entre ces méthodes dépend de la fréquence de mise à jour du fichier. Pour un tableau statique, NB.SI ou la mise en forme conditionnelle suffisent. Pour des données alimentées en continu, Power Query transforme un contrôle ponctuel en processus automatisé. La fonction UNIQUE, elle, occupe un terrain intermédiaire : dynamique, sans macro, mais limitée aux versions récentes d’Excel.
