On a tous vécu la situation : un fichier de suivi client ou de commandes fournisseurs qui grossit semaine après semaine, et un jour on réalise que des lignes sont en double. Le réflexe, c’est d’appliquer une mise en forme conditionnelle sur les doublons pour les voir apparaître en couleur. Sauf que cette méthode, utilisée telle quelle, pose plusieurs problèmes concrets que la plupart des tutoriels ne détaillent pas.
Pourquoi la règle « Valeurs en double » d’Excel pose problème sur le terrain
Dans Excel, le chemin classique passe par Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Valeurs en double. On sélectionne sa plage, on choisit une couleur, et les doublons apparaissent. En apparence, c’est réglé.
A lire en complément : Pourquoi maîtriser Excel ?
En pratique, cette règle ne compare que le contenu brut de chaque cellule dans la plage sélectionnée. Si deux références client sont identiques mais qu’un espace invisible traîne à la fin de l’une d’elles, Excel les considère comme différentes. On passe alors à côté du doublon.
Autre piège fréquent : sur un classeur qui contient plusieurs milliers de lignes, les règles de mise en forme conditionnelle multiples finissent par ralentir le recalcul. La navigation devient saccadée, surtout quand on combine plusieurs règles sur des plages étendues. Des retours d’utilisateurs sur les forums Excel confirment ce phénomène sur des fichiers dépassant quelques dizaines de milliers de lignes.
A lire également : Tableau statistique sur Excel : Création et optimisation en 5 étapes
Mise en forme conditionnelle des doublons avec COUNTIF : la formule qui fiabilise la couleur
Pour gagner en précision, on remplace la règle prédéfinie par une formule NB.SI (COUNTIF) dans une règle personnalisée. La logique : une cellule se colore uniquement si elle apparaît plus d’une fois dans la plage.
Appliquer la règle étape par étape
- Sélectionnez la plage à analyser (par exemple A2:A5000, en excluant l’en-tête).
- Allez dans Accueil > Mise en forme conditionnelle > Nouvelle règle > Utiliser une formule.
- Entrez la formule =NB.SI($A$2:$A$5000;A2)>1 puis choisissez le format de remplissage (couleur de fond, police, bordure).
- Validez. Toutes les cellules de la colonne A qui apparaissent au moins deux fois se colorent instantanément.
Le signe dollar sur la plage ($A$2:$A$5000) verrouille la référence, tandis que A2 sans dollar permet à la formule de s’adapter ligne par ligne. C’est ce détail qui fait fonctionner le mécanisme correctement.
Nettoyer les données avant de chercher les doublons
Avant d’appliquer cette règle, on passe un coup de SUPPRESPACE (TRIM) sur la colonne concernée. On peut aussi forcer la casse avec MINUSCULE ou MAJUSCULE dans une colonne auxiliaire, puis appliquer la mise en forme conditionnelle sur cette colonne nettoyée. Sans ce nettoyage préalable, la détection reste incomplète.
Doublons Excel sur plusieurs colonnes : adapter la formule de couleur
Colorer les doublons sur une seule colonne ne suffit pas toujours. Dans un fichier de facturation, deux lignes peuvent partager le même numéro de référence mais concerner des dates ou des montants différents. On veut alors détecter les doublons sur une combinaison de colonnes.
La méthode consiste à concaténer les valeurs dans la formule de mise en forme conditionnelle. Par exemple, pour repérer les lignes où la colonne A (référence) ET la colonne B (date) sont identiques :
=NB.SI.ENS($A$2:$A$5000;A2;$B$2:$B$5000;B2)>1
NB.SI.ENS (COUNTIFS) accepte plusieurs critères. Cette approche est plus fiable qu’une concaténation manuelle dans une colonne auxiliaire, car elle évite les faux positifs liés au format de date ou aux séparateurs.
Colonne auxiliaire ou mise en forme conditionnelle : quel choix selon le volume de données
Sur un fichier de quelques centaines de lignes, la mise en forme conditionnelle avec NB.SI fonctionne sans problème visible. Au-delà de plusieurs milliers de lignes, les retours varient sur ce point, mais on constate souvent un ralentissement perceptible.
L’alternative : créer une colonne auxiliaire avec la formule =NB.SI($A$2:$A$5000;A2) qui renvoie un chiffre. On applique ensuite la mise en forme conditionnelle non pas sur toute la plage d’origine, mais uniquement sur cette colonne auxiliaire, avec une règle simple du type « supérieur à 1 ». Le calcul se fait une seule fois dans la colonne, et la règle de couleur ne porte que sur des valeurs numériques.
- Fichier de moins de 2 000 lignes : la formule NB.SI directement dans la règle de mise en forme conditionnelle suffit.
- Fichier volumineux : une colonne auxiliaire réduit la charge de recalcul et garde le classeur réactif.
- Classeur partagé sur Excel pour le web : les options de mise en forme conditionnelle y sont plus limitées que sur la version de bureau, la colonne auxiliaire reste la solution la plus portable.
Fonction UNIQUE dans Excel 365 : repérer les doublons sans mise en forme
Depuis Excel pour Microsoft 365 et Excel 2021, la fonction UNIQUE permet de générer dynamiquement la liste des valeurs distinctes d’une plage. On peut alors comparer cette liste au jeu de données complet pour isoler les lignes en double.
En pratique, on place =UNIQUE(A2:A5000) dans une cellule libre. Excel renvoie toutes les valeurs sans répétition. Ensuite, une règle de mise en forme conditionnelle basée sur =ESTNA(EQUIV(A2;$F$2:$F$200;0)) (où F contient le résultat d’UNIQUE) permet de colorer uniquement les doublons, pas les premières occurrences. C’est un avantage net par rapport à la méthode NB.SI classique, qui colore toutes les occurrences y compris la première.
Cette distinction est utile quand on veut supprimer les doublons tout en conservant la ligne d’origine : on ne supprime que les cellules colorées.
Le choix entre NB.SI et UNIQUE dépend de la version d’Excel disponible. Sur Excel 2019 ou antérieur, UNIQUE n’existe pas, et NB.SI reste la méthode la plus fiable pour colorer les doublons. Sur Microsoft 365, combiner UNIQUE et mise en forme conditionnelle offre un contrôle plus fin sur ce qui apparaît en couleur, sans alourdir le classeur avec des colonnes auxiliaires multiples.
